CODEWITHSUNDEEP
sqlsql-serverssis
user3512885
user3512885

Reputation: 99

How would I combine 2 distinct tables into 1 table in SSIS?

Say I have 2 distinct tables in SSIS from 2 different servers.

Table 1                             Table 2

Animal  Age                      Owner     Location


Dog    10                        Bill       IL
Dog     7                        Kelly      CA
Cat     4                        Tom        TX

I want to have one single result table that is

Result Table

Animal  Age  Owner     Location


Dog     10   NULL      NULL
Dog     7    NULL      NULL
Cat     4    NULL      NULL
NULL    NULL Bill      IL
NULL    NULL Kelly     CA
NULL    NULL Tom       TX

Upvotes: 1

Views: 437

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

You should use merge component from SSIS tools. If you want FULL OUTER JOIN you will choose that in the editor of component.

enter image description here

But first of all you need to go to the Input and Output Properties tab and in the OLE DB Source Output set the IsSorted property value to True.(You need to make sure that the input data is truly sorted though.)

Upvotes: 0

Sauget Charles-Henri
Sauget Charles-Henri

Reputation: 461

If you want to join two datasets without any join logic (No key in common) you need to:

  • Create a fake key column in each dataset (With derived column for exemple, value of the fake column should be different in each dataset)

  • Sort these fake key columns

  • Use a full outer join merge based on these fake relations

You make think it's a weird way to do, that is because it's a weird result you are trying to obtain, maybe could you explain your initial need ?

Upvotes: 0

Paolo
Paolo

Reputation: 2254

a UNION should fit:

select animal, age, null as owner, null as location from animal
union
select null as animal, null as age, owner, location from owner

Upvotes: 1

Related Questions