How to compare two tables in SSIS? (SQL Server)
I am creating an SSIS package that will compare two tables and then insert data in another table.
Which tool shall I use for that? I tried to use “Conditional Split” but it looks like it only takes one table as input and not two.
These are my tables:
I want to compare STATUS field in both tables. If Status in TABLE1 is “Pending” and in TABLE2 is “Open” then insert this record in TABLE3.
3 Solutions collect form web for “How to compare two tables in SSIS? (SQL Server)”
If your tables are not large you can use a
Lookup transformation with Full Cache, but I wouldn’t recommend it because if your tables grow you will run into problems. I know I did.
I would recommend
Merge Join transformation. Your setup will include following:
- two data sources, one table each
Merge Jointransformation needs sorted input; I guess you need to match records using ID, so this would be a sort criteria
Merge Jointransformation to connect both (left and right) data flows
Conditional Splittransformation to detect if there are correct statuses in your tables
- any additionally needed transformation (e.g.
Derived Columnto introduce data you have to insert to your destination table)
- one data destination to insert into destination table
This should help, as the article explains the almost exact problem/solution.
I managed to do it by using “Execute SQL Task” tool and writing the following query in it.
INSERT INTO TABLE3 (ID, Status) SELECT * FROM TABLE1 t1, TABLE2 t2 WHERE t1.ID = t2.ID and t1.status = 'Pending' and t2.status = 'Open'
i think so this is what you are looking for.?
In your case if both the tables are Sql tables then follow the steps below
- Drag dataflow task
Edit dataflow task add Oledb source and
in sql command past the below sql code
from table1 a
join table2 b on a.id = b.id
where a.status = ‘Pending’ and b.status = ‘open’
add oledb destination and map the columns with table3
I think this will work for you.