Playing with CURSOR for update

I have a table like this:

CREATE TABLE [dbo].[TestTable](
[TestID] [int] NULL,
[Value] [int] NULL
) ON [PRIMARY]

I populate the table with 50 rows like this:

INSERT INTO [Test].[dbo].[TestTable]
([TestID]
,[Value])
VALUES
(1
,1)
GO 50

The goal is to manipulate the data in a way that the Value column has iterative values from 1 to 50 instead of just values of 1.

 

Solution:

One way to do it is with a cursor which will update the rows. Another way is to write a SSIS package which uses a loop container.

Here is the script for the cursor:

DECLARE @max_id INT
declare @value int
SET @max_id = 1
 
DECLARE cursor_nameĀ  CURSOR FOR
 
SELECT [Value]
FROM dbo.TestTable
for update of [Value];
 
OPEN cursor_name
 
FETCH cursor_name
INTO @Value
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
update dbo.TestTable
set [Value] = @max_id
WHERE current of cursor_name
 
SET @max_id = @max_id + 1
 
FETCH cursor_name INTO @Value
END
 
CLOSE cursor_name
DEALLOCATE cursor_name

It is not the fastest, but this is a good exercise in writing slow code. :)

 

Comments are closed.