We help IT Professionals succeed at work.

TSQL loop through multiple tables

Hi, Expert,

Is it possible use "while" to loop through multiple tables

something like  

while @i <= 5
    insert   into   New_Table_@i
        select *
        from Old_Table_@i
         where .....

end
Comment
Watch Question

Dale BurrellDirector

Commented:
How do you wish to define the list of tables to carry this action out on?
C# ASP.NET Developer
Top Expert 2010
Commented:
Sort of.

just use something like this

declare @x varchar(1000)
declare @ctr int
set @ctr = 1
while @ctr <=5 begin
set @x = REPLACE('INSERT INTO test@@ctr@@ SELECT * FROM oldtest@@ctr@@', '@@ctr@@', @ctr)
exec (@x)
end
Reza RadConsultant, Trainer

Commented:
if you want to use while ,you should get each row with row_number() inside the while ( you can also use CURSUR , but this is not recommended)
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
yup @amenkes, dynamic sql is the way to go.
Don't forget to increase the loop variable

declare @sql nvarchar(1000)
declare @i int
set @i = 1
while @i <=5 begin
set @sql = '
    insert   into   New_Table_'+right(@i,1)+'
        select *
        from Old_Table_'+right(@i,1)+'
         where .....
'
exec (@sql)
set @i=@i+1
end

Author

Commented:
I got it

Thank you so much !!
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Oops. That's what happens when you don't test :-)