Hi Sergey,

Okay, that'll work. Didn't think about using that approach.

Once again, you're quick and right on top of things. Many thanks...


>T-SQL doesn't support variables in table names. You can use dynamic SQL instead.

DECLARE @sql varchar(4000)
>SET @sql = 'update @chvTableName SET ClientID = ClientID + ' + CAST(@insClientOffset AS varchar(16)) +
>', QuoteID        = QuoteID  + ' + CAST(@insQuoteOffset AS varchar(16)) +
>', MasterClientID = MasterClientID + ' + CAST(@insMasterClientOffset AS varchar(16))
>
>EXECUTE (@sql)

>>Hi folks,
>>
>>I have a situation where I must merge several databases into a single database. Each of these (all the sources and the target) have the SAME tables with IDENTICAL structures and relationships between the tables. However, the designers of these databases (in their infinite lack of wisdom) used integer keys which they generate themselves rather than using Identity values. On the good side, there are only three key values which matter -- for the sake of simplicity we'll call them ClientID, QuoteID, and MasterClientID.
>>
>>So, I've put together a nice little sproc to determine the tables that have one or more of these three columns, and I'm working on a sproc to update those columns with new key values (maintaining referential integrity, of course).
>>
>>HERE'S THE PROBLEM: I'd --really-- like to hand the update sproc the (fully qualified) table name to work with. However, the FROM clause doesn't seem to be able to handle it using the following code (this is a sample only, there's more that happens after this):
>>
>>

>>create procedure dbo.updateintkeys
>>@chvTableName          varchar( 50 ),
>>@insClientOffset       smallint,
>>@insQuoteOffset        smallint,
>>@insMasterClientOffset smallint
>>as
>>update @chvTableName
>>set
>>ClientID       = ClientID + @insClientOffset,
>>QuoteID        = QuoteID  + @insQuoteOffset,
>>MasterClientID = MasterClientID + @insMasterClientOffset
>>

>>
>>When I run this, I get "@chvTableName must be defined". If I hard-code the table name, it works perfectly. I ** really ** don't want to write one of these for each table (over seventy of them, and we need this today).
>>
>>Can this be accomplished, and if so, what foolishly simple thing am I doing wrong?