Creating SQL UPDATE statements on the fly

Question

I am in the midst of updating data in multiple tables. Currently I have a table that has one field, "sources", that is just a list of all tables that include the field "itemid". I also have a table that has 2 fields, "itemid" and "olditemid". In TSQL, I would like to iterate through the sources and create the update statements on the fly. Here is what I was trying to do but I get some errors in the update statement that my variable is not declared. I am not sure this is even close the correct way I should be doing this. Ideas?

DECLARE @tblName varchar(50)

DECLARE process_cursor CURSOR FOR 
    SELECT source 
    FROM tmpTableNames

OPEN process_cursor

FETCH NEXT FROM processcursor 
INTO @tblName

WHILE @@FETCH_STATUS = 0

    UPDATE @tblName 
     SET itemid = r.itemid 
     FROM @tblName v, itemref r
     WHERE r.olditemid = v.itemid

    FETCH NEXT FROM process_cursor
    INTO @tblName

END
CLOSE processcursor
DEALLOCATE processcursor

Answer 1

did you try DECLARE @tblName varchar(50)? I would think that would do it.

Answer 2

I don't think you can do it using a variable like that. You could use dynamic SQL for the update:

DECLARE @sql VARCHAR(1000)

SET @sql = 'UPDATE' + @tableName + etc..

EXEC ( @sql )

And just do this inside your cursor.

Answer 3

What you are trying to do is referred to as "dynamic SQL". While you're on the right track, you can't simply stick a variable in place of an object name and execute the query. I'll leave the pitfalls of dynamic SQL to someone else. What you're looking for is this:

DECLARE @tblName varchar(50)

DECLARE process_cursor CURSOR FOR 
    SELECT source 
    FROM tmpTableNames

OPEN process_cursor

FETCH NEXT FROM processcursor 
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(500)

    SELECT @sql = 'UPDATE [' + @tbleName + '] SET itemid = r.itemid FROM [' + @tbleName + '] v, itemref r WHERE r.ilditemid = v.itemid'

    EXEC sp_executesql @sql
    FETCH NEXT FROM process_cursor
    INTO @tblName

END
CLOSE processcursor
DEALLOCATE processcursor

What this does is turn your update query into a string, then passes the SQL contained in that string to the sp_executesql stored procedure (this is the recommended way of executing dynamic sql, rather than EXEC('foo')).

Answer 4

I've never been successful with variable-based UPDATE statements (i.e., UPDATE @tblName), unless I captured them into a string and executed these dynamically, as in:

EXEC 'UPDATE ' + @tblName + '
SET ItemId = (SELECT r.ItemId FROM itemref r WHERE r.OldItemId = ' + @tblName + '.itemId)'

For table TheTable, this should expand to:

EXEC 'UPDATE TheTable
      SET ItemId = (SELECT r.ItemId FROM itemref r WHERE r.OldItemId = TheTable.ItemId)'

Answer 5

You can't execute sql dynamically like this - you need to pass a dynamically generated string into the exec function like this:

DECLARE @tblName varchar(50)

DECLARE process_cursor CURSOR FOR 
    SELECT source 
    FROM tmpTableNames

OPEN process_cursor

FETCH NEXT FROM processcursor 
INTO @tblName

WHILE @@FETCH_STATUS = 0

    Declare @sql varchar(5000)
    Select @sql = 'UPDATE ' + @tblName +  
        'SET itemid = r.itemid 
        FROM ' + @tblName + ' v, itemref r
        WHERE r.olditemid = v.itemid'

    Exec @sql
    FETCH NEXT FROM process_cursor
    INTO @tblName

END
CLOSE processcursor
DEALLOCATE processcursor