asked on

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

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

joriszwaenepoel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

waltersnowslinarnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tigin44

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

Sara bhai

I think above code is not working.

Sara bhai

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

gvamsimba

ASKER

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

ASKER CERTIFIED SOLUTION

Sara bhai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial