November 11, 2012 at 11:26 pm
I want to fetch multiple columns from a table 'table' and compare with Table 'table2' .then update the value in 'table1' with values of 'table2'...
These are inside a procedure .
Which one is best Cursor or Temp table????????????
November 11, 2012 at 11:40 pm
Temptable i think
November 11, 2012 at 11:41 pm
Neither is better.
If your requirement is simply to compare data between two tables then you can do it as a set based operation without using a Cursor.
I would advise the use of Cursors only if don't have any other Set Based option to do the work.
November 12, 2012 at 12:07 am
My concern is the table 'Table1' is filled with large amount of data in minutes. and i need to update that table after the comparion with the 'table2'.
Insertion and updation may occur in parallel.
November 12, 2012 at 12:13 am
You don't need a Cursor to do that..............please post DDL and sample data for both the tables and precisely describe your requirement and someone may come up with a set based solution to your requirement.
Using a cursor/temptable for huge amounts of data will definitely suck.........that could be done in a few seconds using a set based query.
November 12, 2012 at 12:23 am
You can use the UPDATE statement (you can join multiple tables together in an UPDATE statement) or the MERGE statement.
If it's really complex, you can add in a common table expression (CTE) or a temp table, but never ever use a cursor to do this. Especially if you have a lot of data.
But without a concrete scenario, we won't be able to help you out.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2012 at 12:30 am
sumith1andonly1 (11/12/2012)
Insertion and updation may occur in parallel.
How come ?
What there must be inserted (new records ) on which updation will be done.
if latency can be affordable you can use service broker OR script scheduled in job and for synchronous approach trigger can be used though again resource intensive concern
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 12, 2012 at 12:42 am
here is the sample query
DECLARE @Addr1 bigint;
DECLARE @Addr2 bigint;
DECALRE@Timedatetime;
declare GET_Details cursor
for
select COl_Addr1,COl_Addr2 ,COl_Time from TABLE2
open GET_Details;
FETCH NEXT FROM GET_Details INTO @Addr1,@Addr2 @Time;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
UPDATE TABLE1 SET =COl_Addr2
where T1_address =COl_Addr1
COMMIT TRANSACTION
FETCH NEXT FROM GET_Details INTO @Addr1,@Addr2 @Time;
END
CLOSE GET_Details;
DEALLOCATE GET_Details;
November 12, 2012 at 12:51 am
UPDATE t1
SET ColAddr2 = t2.Col_Addr2
FROM
TABLE1 t1
INNER JOIN
TABLE 2 t2
ON t1.T1_Address = t2.Col_Addr1
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2012 at 1:04 am
Looking at the Query you've posted, this will be VERY VERY slow in a cursor as its doing a RBAR itteration over a dataset, on 20-30 rows you might not notice it, but on a few thousand+ expect to go away make a cup of tea and read the morning paper while you wait for it to complete.
In answer to the original question, neither the Cursor or Temp table are the answer. SQL is a set based query language so it works best when manipulating data in sets.
Looking at what you've written you want to update the Address in all rows in Table1 from those that are stored in Table2.
So the simplest way to do this is a Single Update (I'm guessing at the join and update column as they are missing in your code, but it gives the general idea).
Update Target
Set Address2=Source.Col_Addr2
From Table1 Target
JOIN Table2 Sourceon Target.Address=Source.Col_Addr1
Where
Target.Address2!=Source.Col_Addr2
Koen mentioned the Merge statement which in its simplest format is
MERGE Table1 AS Target
USING Table2 AS Source
ON (Target.Address1=Source.Coll_Addr1)
WHEN MATCHED THEN
UPDATE SET Address2=Source.Col_Addr2
One main factor in favour of the using a Merge is that it encourages coders to think more about the sets they are working with as it wont work with If your Source dataset has duplicated rows in it (based on the Join columns)
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 12, 2012 at 1:07 am
Jason-299789 (11/12/2012)
So the simplest way to do this is a Single Update (I'm guessing at the join and update column as they are missing in your code, but it gives the general idea).
Update Target
Set Address2=Source.Col_Addr2
From Table1 Target
JOIN Table2 Sourceon Target.Address=Source.Col_Addr1
Where
Target.Address2!=Source.Col_Addr2
Nice addition of the WHERE clause, didn't think about that. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2012 at 1:16 am
Thanks, its a habit on the Where as Im used to working with huge datasets so anything to limit the scope of the update to the bare minium number of rows.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 12, 2012 at 1:24 am
i need to update another table 'table3' also .(same structure as table1)
?
November 12, 2012 at 1:26 am
I need to update another table 'table3' also ( same structure as 'table1')
November 12, 2012 at 1:27 am
Yes you would have to have a second update for Table3, and the stucture would be the same except that you would change the Table1 references to Table3.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply