CODEWITHSUNDEEP
sqlsql-server
Saeed
Saeed

Reputation: 7370

How to define a variable instead of table name in sql?

I have this SQL query:

delete from Main.dbo.ACTIVITY;
insert into Main.dbo.ACTIVITY
select * from MainTemp.dbo.ACTIVITY;

and I want to execute that SQL for 10 tables after executing for ACTIVITY.

Is there any way to do that? Defining a variable or something?

I'm running the query in SQL Server Management Studio 10 (= SQL Server 2008)

Upvotes: 1

Views: 2897

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294277

Use dynamic SQL

declare @tablename sysname = N'ACTIVITY';
declare @sql  nvarchar(max);

set @sql = N'delete from Main.dbo.' + quotename(@tablename);
exec sp_executesql @sql;
set @sql = N'insert into Main.dbo.' + quotename(@tablename);
exec sp_executesql @sql;
set @sql = N'select * from Main.dbo.' + quotename(@tablename);
exec sp_executesql @sql;

It is very important to use the QUOTENAME when handling dynamic SQL because of the SQL injection risk. Dynamic SQL has pros and cons, for an in dept discussion see The Curse and Blessings of Dynamic SQL.

Use SQLCMD execution mode

SSMS and SQLCMD have capabilities to use client side variable substitution:

:setvar tablename Main.dbo.ACTIVITY
delete from $(tablename);
insert into $(tablename);
select * from $(tablename);

Where SQLCMD mode variables shine is when used in batches because the variables can be passed in via the -v argument. For example:

c:\>for /f %i in (tablenames.txt) do sqlcmd -S <servername> -E -d <dbname> -v tablename=%i -Q "truncate table $(tablename)"

Note that in SSMS the SQLCMD execution mode must be enabled explicitly.

Upvotes: 4

Niladri Biswas
Niladri Biswas

Reputation: 4171

Write dynamic query in that case. The below example may give you some idea

e.g. 

Declare @tableNames table(Id int identity,TableNames Varchar(10))
Insert Into @tableNames Values('Table1'),('Table2'),('Table3'),('Table4')

Declare @query AS Varchar(max)
Declare @tblNames AS Varchar(max)
Declare @count AS int
Declare @i AS int = 1

Select @count = Count(*) from @tableNames

While(@i <=@count)

Begin
    Select @tblNames = TableNames from @tableNames Where Id = @i
    Set  @query = 'delete from ' + @tblNames 
    Set @query +=  '; insert into ' +  @tblNames
    Set @query +=  '; select * from ' +  @tblNames

    print @query --exec  @query 

    Set @i += 1
End

Upvotes: 6

Related Questions