I have two tables in two different databases in two different servers.
Both tables have an
orderid column, and a given value for
orderid references the same entity in both tables.
I want to check whether each value for
orderid in either table has a matching entry in the other table, and write a record to
table3 based on this information.
table3 has the same basic columns as
table2, with the additon of a
discounted price Server2
modifiedon Table 3
The status can be
only in table 1,
only in table 2,
error. If in both
orderid is same the status will be
matching, if it's there only in table 1 it becomes
only in table 1, if found only in table
only in table 2 not sure whether
not matching &
error are relevant in this scenario.
I have crafted an sql for it, just let me know how to use it in SSIS
insert into Table3 (orderid,createdon,modifiedon,status) select COALESCE(t1.orderid,t2.orderid) as orderid, COALESCE(t1.CREATE_DATE,t2.CREATE_DATE) as createdon, COALESCE(t1.UPDATE_DATE,t2.UPDATE_DATE) as modifiedon, case when t1.orderid is null then 'only in table 2' when t2.orderid is null then 'only in table 1' when t1.orderid = t2.orderid then 'matching' when t1.orderid != t2.orderid then 'not matching' else 'error' from Table1 t1 full outer join Table2 t2 on t1.orderid = t2.orderid