komal145 (6/15/2016)
Since Stage table is raw data and destination is key columns we cannot merge Stage and destination.my package is something like below
Stage(Column1 , column2)
|
|
Lookup1(get Column1key1)
|
|
Lookup2(get Column2key2)
|
|
Destination ( Column1key1 , Column2key2 , loaddate)
Can we still use merge and if so , how?
why not?
merge into dest_table dst
using (select substring(rd.record, 10,20) as somevalue
, l1.key1
, l2.key2
from staging_raw_data rd
left outer join lookup1 l1
on l1.keyfield = substring(rd.record,1,4)
left outer join lookup2 l2
on l2.keyfield = substring(rd.record,1,4)
) src
on dst.key1 = src.key1
when matched
then update
set dst.somevalue = src.somevalue
, dst.key2 = src.key2
when not matched
then insert (somevalue, key1, key2)
values src.somevalue
, src.key1
, src.key2
;
if staging_raw_data is not a sql table already, load it into a staging db located on the same instance as the destination table. Avoid using linked servers if possible for this.