How Can We Do Row Based Table Tracking
Nov 2, 2007hi there,
how to retrieve a specific Row from a table based on its row number???
Example:
Select [2nd Row] from TAB001 in SQL Server2000.
regards,
Nilotpal
ADVERTISEMENT
I/O Tracking For A Sql Table?
Jun 16, 2004I'm looking for a way to monitor I/O(select, delete, & updates) to a particular SQL Table for a period of time. Any suggestions?
View 3 Replies View RelatedTracking All Table Modifcations
Apr 26, 2004I am looking for an efficient way to track all modications to a a database. I have come up with a few ideas but none are very efficient.
View 6 Replies View RelatedTracking Table Changes On SQL Server Ce Without Using RDA?
Feb 24, 2007Hi,
Can anybody let me know if there are ways to programatically track changes made to a SQL SERVER CE table? I am writing a db monitoring tool on SQL server CE which should track any changes made to the table.(Insert update and delete)
We could have done this using triggers on Sql Server 2005. Since triggers are not supported on SQL Server CE, are there any alternate ways to achieve this functionality?
Regards,
Ananth
RDA Tracking - No Primary Key On Table
Apr 18, 2007Hello,
Can a table be tracked if it does not have a primary key? I am using rda.Pull method in VS2005 using C#. If not, what are my options? Any help is appreciated.
Thanks.
RDA - Multi Table Error For Tracking
Apr 18, 2007
Hello,
I receive an error message when I try to Push data that the table is not tracked. However, when I try to turn on the tracking option it gives me an error that the table is a multi query table and therefore cannot be tracked. Here is my code to Pull the table.
string TPDAPull = string.Format("SELECT Table1.Field1,Table1.Field2, Table1.Field3, Table1.Field4, from Table1 Left Join Table2 on Table1.Field1 =Table2.Field1 WHERE Table2.Field12='{0}'", this.FindWorker(var));
rda.Pull("Table1", TPDAPull, rdaOleDbConnectString, RdaTrackOption.TrackingOn);
This table does not have a primary key. I was wondering what can I do in this situation? I do not want to Pull the whole table. Any suggestions would be greatly appreciated. I am working in VS 2005, NCF2.0, C#, WM5.0.
Thanks in advance!
SQL Server 2008 :: Change Tracking On Table?
Jun 8, 2015best way to track changes for a very pesky table.We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.
So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.
For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.
The table has tons of fields in it but I only care about the 2 date fields for history purposes.Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):
CREATE TABLE [dbo].[Fake_Name](
[lin] [char](6) NOT NULL,
[boip_no] [char](6) NOT NULL,
[dt_tc] [varchar](25) NULL,
[Code] .....
method to track changes to this table with it being truncated every day?
Tracking Member Views Of Clan Pages (was Table Design Help Needed)
Feb 28, 2005I am helping a friend with a gamming website. There are pages which displays data from other clans.
Members need to register and login to view full clan details. User who have not logged in can only view partial data about a clan.
I need to keep track of the kind of hits each page received. That is I want to tell say "Clan X" that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates.
I am using ASP with MS SQL.
I would like some help on designing the table layout that is efficient for keeping track of the visitors for each page.
Any help will be appreciated.
Add Calculated Field In Order Table Based On Price Column In Product Table
Nov 18, 2014I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID
Create Target Table Dynamically Based On Source Table Schema?
Sep 13, 2005I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
Query Based Off Primary Key Of Parent Table - Adding Child Table
Jan 28, 2012I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
SQL Server Admin 2014 :: Insert A Row To A Table Based On Table Values?
Jun 10, 2015Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
Delete Child Table Rows Based On Predicates In A Parent Table
Jul 20, 2005I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View 1 Replies View RelatedAnalysis :: Hierarchy Based On Dimension Table Joined Multiple Times Against A Fact Table?
Aug 11, 2015I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.Â
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.Â
Transact SQL :: Update Table Based On Available Date Range In Same Table
Dec 2, 2015I would like to update the flag of the promotion ID should the promotion ID date range overlap with Promotion ID(All) Date Range. The general logic is as below.
Update TableName
SET PromotionID Flag = 1 AND Reason = 'Overlap with row ID(Overlap row ID number)'
Where EACH ROW(Except with Promotion ID ALL) Date Range Overlap with ROW(with promotion ID ALL) Date range
Note: ROW is Partition By ColumnA,ColumnB
TableName: PromotionList
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 0 NULL
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 0 NULL
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Expected outcome after performing update on the table
ID PromotionID StartDate EndDate ColumnA ColumnB Flag Reason
1 1 2015-04-05 2015-05-28 NULL NULL 1 Overlap with row ID 4
2 1 2015-04-05 2015-04-23 2 3 0 NULL
3 2 2015-05-04 2015-07-07 2 3 Overlap with row ID 5
4 ALL 2015-04-05 2015-04-28 NULL NULL 0 NULL
5 ALL 2015-07-06 2015-07-10 2 3 0 NULL
6 1 2015-02-03 2015-03-03 NULL NULL 0 NULL
Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria
Jan 22, 2008Hello Friends,
I have two tables, And also I have Sample data in them.
create table X
(y int,
m int,
v int)
insert into X select 2007,1,5
insert into X select 2007,1,3
insert into X select 2007,2,9
insert into X select 2007,2,1
select * from X
Create table Y
(fy int,
fm int,
v int)
insert into Y select 2007,1,0
insert into Y select 2007,2,0
insert into Y select 2007,3,0
select * from X
select * from Y
I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m
Using temporary table cannot be done.
Thanks in Advance,
Babz
Selection Of Table Based On Value Selected For The Particular Attribute In Master Table
May 29, 2008
Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected .
So how can I do this ???
How to form a realtion between the tables,... ????
SQL 2012 :: Delete From One Table Based On Results Of Other Table
May 28, 2015I have this table:
with actividades_secundarias as (
select a.*, r.Antigo, r.Novo, rn = row_number()
over (PARTITION BY a.nif_antigo, r.novo ORDER BY a.nif_antigo)
from ACT_SECUNDARIAS a inner join
((select
[Code] ....
I want to make a delete statement like this:
select * into #table1 from actvidades_secundarias where rn>1
Delete from act_secundarias where act_secundarias.nif_antigo = #table1.nif_antigo and act_secundarias.cod_cae = #table1.cod_cae
But it seems that I cant delete like this.
Transact SQL :: Update A Field In Table Based On Another Table
Nov 17, 2015I have a daily record table (has field Date1, field2Update) and have another table(has field Date2, Flag (value: 0 and non 0 values)
I want to update table1 and set field2Update as somevalue where table2.Flag <> 0
How To Insert Rows To Table B Based On Values In Table A.
Mar 7, 2008
I need Insert rows in the OrderDetails Table based on values in the Orders Table
In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD
Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.
I need a SQL Script to accomplish this. thanks
Finding Rows In One Table But Not In Another Table Based On 2 Fields
Apr 23, 2008
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
How To Update A Table 2 Based On A Nother Table
Feb 20, 2001hi, I have two tables named state and state2, please click on the link to view both tables. I want to update the second table by inserting the
state from table one. I tried to make it but failed,I would appreciate your
help.
http://66.61.28.119/test/state.asp
update state2
set state = (select code from state1 where id= select id from state1 where...I do not know how
where id =(select id from state.....I do not know how
anyway, I appreciate your help.
Ali
Update Table Column Based On Value From Another Table?
Sep 2, 2005Hi, I have two tables. I want to update two columns in my first table,[ADD_BSL_SALES] and [ADD_BSL_COST] with two values [Sales] and[Costs] held in my #temp table but based on a RUN_DATE from my firsttable.Can anyone point me in the right direction?Thanks in Advance ï?ŠBryanCREATE TABLE [GROSMARG_AUDIT_ADDITION] ([RUN_DATE] [datetime] NULL ,[SALES_DIFF] [numeric](19, 6) NULL ,[COST_DIFF] [numeric](19, 6) NULL ,[ADD_BSL_SALES] [numeric](18, 0) NULL ,[ADD_BSL_COST] [numeric](18, 0) NULL ,[ADD_SALES_DIFF] [numeric](18, 0) NULL ,[ADD_COST_DIFF] [numeric](18, 0) NULL) ON [PRIMARY]GOINSERT RUN_DATE,datetime,INSERT SALES_DIFF,numeric(19,6),INSERT COST_DIFF,numeric(19,6)INSERT ADD_BSL_SALES,numeric(18,0),INSERT ADD_BSL_COST,numeric(18,0),INSERT ADD_SALES_DIFF,numeric(18,0)INSERT ADD_COST_DIFF,numeric(18,0)--- Second TableCREATE TABLE #DUPTOTALS[Sales][Costs]
View 1 Replies View RelatedTrying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
Nov 1, 2007I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
Update The Second Table Based On First Table?????????
Jun 29, 2007I have two tables.First-----RollNo Number(PK) Appno Number Second-------RollNo NumberAppno_1 NumberNow I want to update "Second" table's "Appno_1" field with the "Appno" field of the "First" table and the "RollNO" of "First" table should match with the "Second" table "RollNo" field.Actually both the tables are different .but by mistake i have entered some blank data in second table .So i want to update the second table.Plz Help...............How i will i do it :Plz Help-----------------------
View 2 Replies View RelatedHow To Delete From Table A Based On Table B
Nov 17, 2005lets say i have 100 employees on Table A and I have 10 employees on Table B. I want to delete all the employees in Table A that are in Table B. Delete From TableA Where EmpNum = (Select EmpNum From TableB)The above SQL wont work but i am looking for something similar. any ideas?
View 1 Replies View RelatedUpdate Table Based On Another Table
Feb 20, 2001hi, I have two tables named state and state2, please click on the link to view both tables. I want to update the second table by inserting the
state from table one. I tried to make it but failed,I would appreciate your
help.
http://66.61.28.119/test/state.asp
update state2
set state = (select code from state1 where id= select id from state1 where...I do not know how
where id =(select id from state.....I do not know how
anyway, I appreciate your help.
Ali
Based On Id, How To Know Table Name
Dec 4, 2007Dear All,
based on objectid column in sysindexes, how to know the table name?
select object_name(id) as table_name, rows from sysindexes
where indid<2 order by rows desc
actually i'm trying to replace the query
select count(*) from mytable
with
select object_name(id) as table_name, rows from sysindexes
where indid<2
query
please guide me
Vinod
Even you learn 1%, Learn it with 100% confidence.
Web Based SQL Table Editor
Jun 21, 2004Is anyone aware of a tool that will allow users to update the values of a sql table via a web page? What I am looking for is a tool that when given the name of a table will generate a web front end to allow editing of the table.
Thanks,
Todd
Updating A Row Based On Another Row In Table
Aug 6, 2013I have a table in SQL server which has two rows. One has an ID of 'Bag CL55412'. Another has an ID of 'Bag CL55412-Cpy'. The Price for the first one is $99.99. I want to make the price for the second one 2% more & $.99 more.
The data looks like this
IDChannelPriceCompanyID
Bag CL5541299.99111
Bag CL55412-Cpy102.99500
The SQL to select that formula looks like this.
SELECT [ID]
,[ChannelPrice]
,(CEILING([ChannelPrice]*1.02)+.99)
,CompanyID
FROM [SC].[dbo].[Product]
WHERE ID like '%CL55412%'
To Update, I can think of something like this, but it will update based on itself, not a different row in the table.
UPDATE [SC].[dbo].[Product] SET ChannelPrice=(CEILING([ChannelPrice]*1.02)+.99)
How would I get it to update as I want it to? The origin CompanyID will always be 111 & the destination company ID will always be 500 for all of the respective rows that need to be updated
Max Query( Based On Two Table)
Mar 19, 2007i've two table Rate and Topic
Sample Table
Table name==>Topic
---------------------
Topic|TID|
-----|---|
hddgg|1
trgsd|2
vnvnv|3
sdfsd|4
dfsdf|5
dsdff|6
........
........
-----------------
above table TID is primary key
next table
Table Name==>Rate
-------------------
RID|TID|Rate
---|---|--------
1--|2--|4
2--|5--|3
3--|2--|3
4--|2--|2
5--|1--|1
6--|2--|1
7--|2--|2
8--|5--|3
9--|1--|4
.........
........
above table RID is primary key and TID is foreign key..
i need Top 3 Topic but based on Maximum Rate of value
any query.........
Update One Based On ANother Table
Oct 13, 2005Here is my situation;I have two tables in a MS-SQL DB. One table with dollar amounts and servicecodes. I have a second table that I want to move some information into fromthe first table. The catch is I want to move one field as is from the firsttable to the second, but the rest of the fields in the second table arecalculations based on fields in the first table.The first table is called XFILE. It has fields SVCCODE, PRICE, DWAGES,DMATLS, etc. The second table has the same field names and I want to movethe SVCCODE from XFILE to Cost_Percent with no changes. For DWAGES in theCost_Percent table I want to do the following calculation;[ XFILE.DWAGE] divided by [XFILE.PRICE] and put the results in Cost_Percenttable DWAGES fieldSo basically I am putting a percent in the Cost_Percent table. I can movethe data from one table to another ok, but I can not figure out how to writethe query in the Query Analyzer to do this.I am ruining SQL2000 Standard on a Win2K3 server. I am using Query Analyzerand SQL Enterprise Manager from an XP-Pro WS.I have looked in the 'Books On-Line' for the answer but I sort of new to SQLand can't find the answer that I am sure is staring me in the face.Thanks in advance for any help.Mike Charneym charney at dunlap hospital dot org
View 5 Replies View RelatedHow Do U Create A Table Based On A Query
Jan 23, 2002Hi guys,
Can anyone please tell me how to create a table based on a query.
Thanks,
Joe