how to add multiple tables data into single tableusinf for each loop

Question

Post reply

how to add multiple tables data into single tableusinf for each loop

Praxy

Mr or Mrs. 500

Points: 573
More actions
May 13, 2008 at 12:08 am

#154344

Hi I m Prasanna here,
Please someone help me out
I have many tables like food1,food2,food3,food4,food5
i have a single table in destination called "food_detail"
what i want to do is just to load all the data of the food tables to single table "food
_deatil" using foreach loop or some easy and best way for loading

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jaypee_s Right there with Babe Points: 793 More actions · Answer 1

Hi

use union...

See the example as follows:

create table #a

( sno int not null,

sname varchar(10)

)

create table #b

( sno int not null,

sname1 varchar(10)

)

create table #c

(

sno int not null,

sname varchar(10)

)

insert into #c

select * from #a

union

select * from #b

insert into #a values (1,'a')

insert into #a values (2,'b')

insert into #a values (3,'c')

insert into #b values (4,'a')

insert into #b values (5,'b')

insert into #b values (6,'c')

insert into #c

select * from #a

union

select * from #b

Thanks

jaypee.s

Praxy Mr or Mrs. 500 Points: 573 More actions · Answer 2

Hi Jaypee.S

thanks for u r concern

but i just want to transfer this using SSIS

I have an idea of using foreachloop container and using variable to check all the food tables and then

transfering the food data into common table in destination caleed "food_deatil"

but just suggest me how to do it using that foreachloop

thanks prasanna

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You can use a string variable as the sourceSQL of your data flow component. Make the string variable an expression and paste together the table number your are on or loop through the table names themselves.

"SELECT * FROM " + @[User::Table]

However, just making the sourceSQL of your data flow a big UNION ALL as suggested will probably be easier, faster, and ultimately work better.

Joseph-465703 SSCrazy Points: 2122 More actions · Answer 4

all the 4 tables are and destination are in same databases?