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)
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
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.
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.
