using variable for table name in t-sql

dhenderson12
dhenderson12 used Ask the Experts™
on

Can I use a variable for a table name in t-sql?  here's what I'm trying to do:

declare @type int
declare @tableName varchar(100)
declare @loopCounter int

while @loopCounter < 3
begin
   @loopCounter = @loopCounter + 1

   if @loopCounter = 1 select @tableName = 'table1'
   else if @loopCounter = 2 @tableName = 'table2'

   update @tableName
   set call_id = 10

end

I need to update two different tables with the same data having the same column name(s)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
declare @type int
declare @tableName varchar(100)
declare @loopCounter int

set @loopCounter = 0

while @loopCounter < 3
begin
set @loopCounter = @loopCounter + 1

SET @tableName = 'table' + CAST(1 AS varchar)
EXEC('update [' + @tableName + '] set call_id = 10')

end
Reza RadConsultant, Trainer
Commented:
you must use dynamic sql
i mean build your sql query and then execute it with
sp_execsql (@sql)
Top Expert 2012

Commented:
And you must have UPDATE permissions on the table in order to use Dynamic SQL.  Not good.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial