Cursor output to cursor input - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Cursor output to cursor input

Author

Topic

stvns78
Starting Member

2 Posts

Posted - 2011-10-25 : 10:28:44

I need to run an update statement against one table about 600 times for each row in that table, example:

TableName ColumnName ID
1. table1 column1 1
2. table2 column2 1

select max(ColumnName) from tablename

the update statement would say:

update tablex set ID = # where tablename = tablename

I have created a cursor that returns the table name and column name for each table, but i then need to use the TableName and ColumnName for each row in that cursor as an input variable for the update statement.

I hope i explained this well enough.

Thanks

stvns78
Starting Member

2 Posts

Posted - 2011-10-25 : 11:01:45

This is what i have thus far:

DECLARE @tablename nvarchar(50), @columnname nvarchar(50), @maxid int, @updatesql varchar(4000);

DECLARE tables_cursor CURSOR FOR
SELECT tbname, name
FROM maxsequence
WHERE tbname in (select objectname from maxobject where persistent = 1);

OPEN tables_cursor;

FETCH NEXT FROM tables_cursor
INTO @tablename, @columnname;

WHILE (@@fetch_status = 0)
BEGIN
DECLARE select_cursor CURSOR FOR
select max(@columnname) from @tablename;

OPEN select_cursor;

FETCH NEXT FROM select_cursor INTO @maxid;

WHILE @@FETCH_STATUS <> 0
BEGIN
SET @updatesql = 'update maxsequence set maxreserved = ' + @maxid + ' where tbname = ' + @tablename;
exec @updatesql;
FETCH NEXT FROM select_cursor INTO @maxid;
END

END
CLOSE select_cursor;
DEALLOCATE select_cursor;
CLOSE tables_cursor;
DEALLOCATE tables_cursor;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources