Concatenate table name loop sql server

Asked

Viewed 1,073 times

4

I’m trying to fill a table of mine with a loop in sql server follows the code:

declare @i int
set @i =1
while @i < 5
begin
INSERT INTO TABELA VALUES('teste')
set @i =  @i + 1
end

I would like to concatenate there the name of the table in the Insert with the number of the variable @i

Creating Tabela1,table2,Tabela3 ...etc

sql sql-server

2 answers

4


You can run any dynamic query on SQL Server using the routine sp_executesql.

Example:

declare @i int
declare @sql nvarchar(500)
set @i =1
while @i <= 5
begin

    set @sql = N'INSERT INTO TABELA' 
      + CONVERT(VARCHAR, @i) 
      + ' VALUES(''teste ' 
      + CONVERT(VARCHAR, @i) + ''')'
    execute sp_executesql @sql
    set @i =  @i + 1

end

Demo on the Sqlfiddle

1

declare @i int
set @i =1
while @i < 5
begin
INSERT INTO TABELA VALUES('tabela' + CAST(@i AS VARCHAR))
set @i =  @i + 1
end

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.