Inserting data into Multiple tables in TSQL

HI,
   I have 10 tables named mytable_1,mytable_2,mytable_3
and so on
with similar schemas..I want to insert similar data into these 10 tables..
insert into mytable_1
select * from urtable...instead of using the insert statement
10 times, is there is way i can insert the data into
all 10 tables in single query ?

Many Thanks
gvamsimbaAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @i int
set @i = 1
declare @sql varchar(1000)
WHILE @i <=10
BEGIN
  SET @sql = 'insert into myTable_'+cast(@i as varchar ) + '  Select * from urTable '
  EXEC (@sql)
  SET @i = @i +1
END
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Not without dynamic query,


declare @i int
set @i = 1

WHILE @i <=10
BEGIN
  EXEC ( 'insert into myTable_'+cast(@i as varchar ) + '  Select * from urTable ' )
  SET @i = @i +1
END
0
 
gvamsimbaAuthor Commented:
Thanks Aneesh,but i am getting the below error message -

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'cast'.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
gvamsimbaAuthor Commented:
Hi aneesh, your updated query works fine, but it is doubling
the figures..for example if 100 rows are being inserted
into table1, 200 rows are being inserted into table2
and so on, which should not happen..

Thanks.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you paste the exact query ?
0
 
gvamsimbaAuthor Commented:
Here is the query..i did not make any change to ur updated query...

declare @i int
set @i = 1
declare @sql varchar(1000)
WHILE @i <=10
BEGIN
  SET @sql = 'insert into myTable_'+cast(@i as varchar ) + '  Select * from urTable '
  EXEC (@sql)
  SET @i = @i +1
END
0
 
gvamsimbaAuthor Commented:
This is perfect..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.