how to do this without a cursor:

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to do this without a cursor:

Author

Topic

Non-conformer
Starting Member

14 Posts

Posted - 2006-03-15 : 15:17:04

Ok gurus, here's a common situation where I find myself (reluctanly) using a cursor. It looks for a record in TABLE2 based on values from TABLE1; if found, it updates, otherwise inserts.

How would you do this withOUT a cursor?

declare C1 cursor for
select ID, X from TABLE1
open C1
fetch next from C1 into @ID, @X
while @@fetch_status=0 begin
select @C=count(*) from TABLE2 where ID=@ID
if @C=0
insert into TABLE2 values (@ID, @X)
else
update TABLE2 set X=@X where ID=@ID
fetch next from C1 into @ID, @X
end
close C1
deallocate C1

Keep it simple.
NC

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-15 : 15:27:41

Just do 2 commands, one for all your inserts one for all your updates.


INSERT INTO Table2(ID, X)
SELECT Table1.ID, Table2.X
FROM Table1
WHERE NOT EXISTS(SELECT * FROM Table2 a WHERE a.ID = Table1.ID)

UPDATE a
SET a.X = b.X
FROM Table2 a INNER JOIN Table1 b ON (a.ID = b.ID)

Non-conformer
Starting Member

14 Posts

Posted - 2006-03-15 : 15:34:43

Thanks! Just what I needed!

Keep it simple.
NC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-16 : 01:25:31

In the above code, change the order
1 Update
2 Insert

Madhivanan

Failing to plan is Planning to fail

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-16 : 13:47:11

quote:
Originally posted by madhivanan

In the above code, change the order
1 Update
2 Insert

Madhivanan

Failing to plan is Planning to fail

Oops. That makes sense else the rows that were just inserted will be updated when they don't need to be.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-16 : 20:03:41

"Oops. That makes sense else the rows that were just inserted will be updated when they don't need to be."
Yes if you perform unconditional update.

UPDATE a
SET    a.X  =  b.X
FROM   Table2 a INNER JOIN Table1 b 
ON     a.ID =  b.ID
WHERE  a.X  <> b.X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources