Updating one table from another using CURSORs

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)

Updating one table from another using CURSORs

Author

Topic

mr MO
Starting Member

2 Posts

Posted - 2002-06-13 : 05:04:03

Hi Folks

Is this the best way for Updating numerous values in one table with values in another when the ID's match. I have a large table to update.

The below method works for me but I'd like to know if it is aesthetically correct.

Cheers for any comments and aplogies if I've missed something obvious

MO

--CREATE PROCEDURE sp_MultiCursorExample AS

--We dclare variables and their
--DATATYPE's
DECLARE @Test1ID nVarchar (50)
DECLARE @Test2ID nVarchar (50)
DECLARE @TestVal1 nVarchar (50)
DECLARE @TestVal2 nVarchar (50)

--We allocate our CURSOR's a
--variable to work with
DECLARE cTable_1_ID CURSOR FOR
SELECT [ID]
FROM tbl_Test1

DECLARE cTable_2_ID CURSOR FOR
SELECT [ID]
FROM tbl_Test2

DECLARE cTest_Val_1 CURSOR FOR
SELECT Value1
FROM tbl_Test2

DECLARE cTest_Val_2 CURSOR FOR
SELECT Value2
FROM tbl_Test2

--We OPEN our CURSORS
OPEN cTable_1_ID
OPEN cTable_2_ID
OPEN cTest_Val_1
OPEN cTest_Val_2

--This starts off our values
FETCH NEXT FROM cTable_1_ID
INTO @Test1ID
FETCH NEXT FROM cTable_2_ID
INTO @Test2ID
FETCH NEXT FROM cTest_Val_1
INTO @TestVal1
FETCH NEXT FROM cTest_Val_2
INTO @TestVal2

--Status will remain '0' as long as
--more row's exist keeping our query
--running
WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE tbl_Test1
SET Value1 = @TestVal1, Value2 = @TestVal2
WHERE [ID] = @Test2ID

--Fetch next value from our CURSORS and
--insert into our declared variables for
--use in the query
FETCH NEXT FROM cTable_1_ID
INTO @Test1ID
FETCH NEXT FROM cTable_2_ID
INTO @Test2ID
FETCH NEXT FROM cTest_Val_1
INTO @TestVal1
FETCH NEXT FROM cTest_Val_2
INTO @TestVal2

END

--Close CURSORS
CLOSE cTable_1_ID
CLOSE cTable_2_ID
CLOSE cTable_Val_1
CLOSE cTable_Val_2
--Clear CURSORS
DEALLOCATE cTable_1_ID
DEALLOCATE cTable_2_ID
DEALLOCATE cTable_Val_1
DEALLOCATE cTable_Val_2

--GO

Edited by - mr MO on 06/13/2002 06:14:23

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-13 : 05:22:45

This may be a little easier on the eyes....

UPDATE tbl_Test1
SET tbl_Test1.Value1 = T2.Value1,
tbl_Test1.Value2 = T2.Value2
FROM tbl_Test2 T2
WHERE tbl_Test1.[ID] = T2.[ID]

Peace

Rick

mr MO
Starting Member

2 Posts

Posted - 2002-06-13 : 06:18:29

cheers Rick, MO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources