create multiple tables using loop in sql

Question

I would like to create multiple tables with same column names. The name of the tables would be table1, table2, table3, etc. Can someone help me correct the part 'table@cnt' in codeline 4?.

declare @cnt int = 0;
while @cnt < 7
begin 

CREATE TABLE table@cnt(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)   
);

set @cnt = @cnt + 1;
end;

use dynamic sql with exec() i.e. 'CREATE TABLE table' + convert(varchar(10), @cnt) + '( PersonID int,' — artm
– artm, Commented Apr 21, 2017 at 4:25
@artm thank you for your prompt help. can you please elaborate more? — Sonam
– Sonam, Commented Apr 21, 2017 at 4:37
As artm mentioned, you'd replace CREATE TABLE table@cnt(PersonID int...); with EXEC('CREATE TABLE table' + convert(varchar(10), @cnt) + '(PersonID int...);'); — ZLK
– ZLK, Commented Apr 21, 2017 at 4:57
@Sonam Please try to search .... You had keyword dynamic sql from @artm — TriV
– TriV, Commented Apr 21, 2017 at 4:59

artm · Accepted Answer · 2017-04-21 04:57:32Z

3

declare @cnt int = 0;
while @cnt < 7
begin 

declare @q nvarchar(max) = 'CREATE TABLE table' + convert(varchar(10), @cnt) + '(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)   
);'

exec (@q)

set @cnt = @cnt + 1;
end;

answered Apr 21, 2017 at 4:57

artm

8,5943 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sonam Over a year ago

it worked like a magic. thanks a lot for your help. have a good day.

TriV · Accepted Answer · 2017-04-21 07:10:59Z

0

You could use dynamic query like this

DECLARE @cnt int = 1
DECLARE @TableName varchar(20) = CAST(@cnt as varchar(20))

DECLARE @query nvarchar(max) = 
                   N'CREATE TABLE '+  @TableName + 
                   ' (
                   PersonID int,
                   LastName varchar(255),
                   FirstName varchar(255),
                   Address varchar(255),
                   City varchar(255)   
                   )'
EXEC (@query)

edited Apr 21, 2017 at 7:10

answered Apr 21, 2017 at 4:57

TriV

5,1582 gold badges12 silver badges18 bronze badges

3 Comments

user7715598 Over a year ago

The above throws error Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'table'.

user7715598 Over a year ago

But it will creates only One table it deosn't loop over as tab1 ,tab2 ...

TriV Over a year ago

You could use it with WHILE ... in OP's post.

user7715598 · Accepted Answer · 2017-04-21 07:15:44Z

0

DECLARE @cnt int = 1
While(@cnt <=10)
Begin
DECLARE @query nvarchar(max) = 
                   N'CREATE TABLE '+'table_'+CASt(@cnt  AS VARCHAR(10))+
                   ' (
                   PersonID int,
                   LastName varchar(255),
                   FirstName varchar(255),
                   Address varchar(255),
                   City varchar(255)   
                   )'

EXEC (@query)
SET @cnt=@cnt+1
END

answered Apr 21, 2017 at 7:15

user7715598

Collectives™ on Stack Overflow

create multiple tables using loop in sql

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related