Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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
0
gvamsimba
Asked:
gvamsimba
  • 4
  • 3
1 Solution
 
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
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now