two source table columns to one destination table

Question

two source table columns to one destination table

ahmed7.bi

Say Hey Kid

Points: 713
More actions
December 3, 2010 at 3:05 am

#143750

Hi
i have table tab1 with columns a,b,c and table tab2 with columns x,y,z now i have to load the data into tab3 with columns a,b,x,y
so i have created two oledb sources (tab1 & tab2) and a oledb desitination(tab3) now which transformation should i use to get two columns(a,b) form tab1 and two columns(x,y) from tab2 and load into destination table tab3(a,b,x,y)
please let me know how to achieve this

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Feed the two sources into a Merge Join transformation and from there map to your destination table (I'm assuming that the two source tables can be joined, of course).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246965 More actions · Answer 2

Or ...

Instead of using the tables as your sources, use a query which includes the join. Then your data source is ready to go - this will be faster than MERGE JOIN in SSIS.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ahmed7.bi Say Hey Kid Points: 713 More actions · Answer 3

Phil Parkin (12/3/2010)
Or ...
Instead of using the tables as your sources, use a query which includes the join. Then your data source is ready to go - this will be faster than MERGE JOIN in SSIS.

HI Thanks for the reply

it worked by selecting outer join but out put is

1210NULLNULL

NULLNULL66667

NULLNULL98665

377467NULLNULL

123156NULLNULL

iam expecting the below one

a b x y

121066667

37746798665

123156NULLNULL

how to get like the above one

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

If you have two data sets

Set 1

1, 2

3, 4

Set 2

a, b

c, d

and you want to combine them to give something like

1, 2, a, b

3, 4, c, d

you need to find a way of linking set 1 and set 2. Otherwise who is to say that

1, 2, c, d

3, 4, a, b

is not correct?

So ... what is the link between your source data tables?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ahmed7.bi Say Hey Kid Points: 713 More actions · Answer 5

thanks a ton now i got it u mean to say that there should be a common key to join both the tables

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

Yes exactly. Once you have that, you're in business.

Even if you just use Row_Number() to generate one on the fly ... (if you're not too fussy about which rows end up together).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin