[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Dynamic TSQL Split


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
SOME of  these tables based on a condition as you can see
in my below code..

        i want to insert only into those tables based on the
value of the 'cell'column in tableb..for instance if the
cell=2 in tableb, then i need to insert the records only
in mytable_2 and NOT in any other table , if cell=3
then insert only into mytable_3 and so on...
     
        Can anybody please re-write my code to get the
above result if possible ?

Many Thanks
declare @i int 
set @i = 1 
declare @sql varchar(1000) 
WHILE @i <=10
BEGIN
  SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' select *
 from tableb where cell =2 '
 EXEC(@sql) 
  SET @i = @i +1 
END

Open in new window

0
gvamsimba
Asked:
gvamsimba
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
declare @i int 
set @i = 1 
declare @sql varchar(1000) 
WHILE @i <=10
BEGIN
  SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' select *
 from tableb where cell = '+cast(@i as varchar(2)) 
  EXEC(@sql) 
  SET @i = @i +1 
END

Open in new window

0
 
joriszwaenepoelCommented:
I think you need to modify

SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' select *
 from tableb where cell =2 '

to

SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' select *
 from tableb where cell = ' +cast(@i as varchar(2))


0
 
waltersnowslinarnoldCommented:
try the following code..,
declare @i int 
set @i = 1 
declare @sql varchar(1000) 
DECLARE @value VARCHAR(MAX)
WHILE @i <=10
BEGIN
  SET @value = SELECT * FROM tableb WHERE cell = @i
  SET @sql = 'insert into mytable_'+cast(@value as varchar(2))
 EXEC(@sql) 
  SET @i = @i +1 
END

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
tigin44Commented:
try this
declare @i int 
set @i = 1 
declare @sql varchar(1000) 
WHILE @i <=10
BEGIN
  SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' '+
			 'select * ' +
			 'from tableb where cell =' +cast(@i as varchar(2))
  EXEC(@sql) 
  SET @i = @i +1 
END

Open in new window

0
 
sarabhaiCommented:
I think above code is not working.
0
 
sarabhaiCommented:
YOU JUST forget cell value which is variable not constant.

declare @i int
set @i = 1
declare @sql varchar(1000)
WHILE @i <=10
BEGIN
  SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' select *
 from tableb where cell =  ' +cast(@i as varchar(2))
 EXEC(@sql)
  SET @i = @i +1
END

0
 
gvamsimbaAuthor Commented:
Thank you guyz, but if the cell value in tableb=0,
then i want to insert the records into all
the 10 tables...can any one give me the updated code please ?

many thanks
0
 
sarabhaiCommented:

DECLARE @i int
set @i = 1
declare @sql varchar(1000)
WHILE @i <=10
BEGIN
  SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' select *
 from tableb where cell =  ' +cast(@i as varchar(2))
 EXEC(@sql)
  SET @sql = 'insert into mytable_'+cast(@i as varchar(2)) + ' select *
 from tableb where cell = 0 '
 EXEC(@sql)
  SET @i = @i +1
END

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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