asked on

Variables for table and column names using the UPDATETEXT command

I'm trying to use updatetext to replace some text in text columns in a number of different tables. Obviously there is some looping involved. At the heart of the loop I want to execute something like this:

      updatetext @TableName.@ColumnName @ptr @pos @txtlen @ntxt

I get this error using this arrangement:

      Server: Msg 170, Level 15, State 1, Line 134
      Line 134: Incorrect syntax near '@TableName'.

If I use an actual table name and column name, the statement appears to work. However, I need to be able to substitute a new table name for each loop, so I'm back to the drawing board if I can't use a variable instead.

1) Is it not possible with the updatetext command to use variables for the table and column names?

2) If it's not possible, what other approach might I take to automate this task?

Thanks.

Scott

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

conderscott

ASKER

angelIII:

Thanks for the comment. You're right. I have tried the dynamic route and it seems to fail due to the binary @ptr variable.

Another idea: if I loaded the records one at a time into a temp table, could I use updatetext on the temp table? That way the table would always have the same name.

Guy Hengel [angelIII / a3]

sorry, but I had tried that also ... no go.