we have an DWH input layer and want to merge into our core layer with insert/updates.
Now we have about 30 key-value lookup tables and wonder how to look up the primary keys to insert
those as foreign keys.
MERGE INTO xxxxdb.dbo.s_core CORE
USING (
SELECT mer,ssl,filename
FROM AL.dbo.s_al acq
) acq
ON (CONCAT(core.mmer,CORE.sslcode) = CONCAT(acq.mer,acq.ssl))
WHEN MATCHED THEN
UPDATE SET
CORE.filename = acq.filename
WHEN NOT MATCHED THEN
INSERT (
mer,
sslcode,
filename,
--foreignkey_newlookupvalue1
--foreignkey_newlookupvalue2
--foreignkey_newlookupvalue3
--foreignkey_newlookupvalue4
--foreignkey_newlookupvalue5
--foreignkey_newlookupvalue6
--foreignkey_newlookupvalue7
--foreignkey_newlookupvalue..
--foreignkey_newlookupvalue30
) VALUES (
acq.mer,
acq.ssl,
acq.filename,
--select primarykey_newlookupvalue1 from lookuptable1 where acq.mer = lookuptable1.mer
--select primarykey_newlookupvalue2 from lookuptable2 where acq.mer = lookuptable2.mer
--select primarykey_newlookupvalue3 from lookuptable3 where acq.mer = lookuptable3.mer
--select primarykey_newlookupvalue4 from lookuptable4 where acq.mer = lookuptable4.mer
--select primarykey_newlookupvalue5 from lookuptable5 where acq.mer = lookuptable5.mer
--select primarykey_newlookupvalue6 from lookuptable6 where acq.mer = lookuptable6.mer
--select primarykey_newlookupvalue7 from lookuptable7 where acq.mer = lookuptable7.mer
--select primarykey_newlookupvalue.. from lookuptable.. where acq.mer = lookuptable...mer
--select primarykey_newlookupvalue30 from lookuptable30 where acq.mer = lookuptable30.mer
);