0

I've got problem with MERGE Statement. I am comparing 2 tables. WHEN MATCHED UPDATE works like charm, but there's a problem with WHEN NOT MATCHED THEN INSERT clause. I want to insert rows to daSRC (source) table but I need to have the same da_DocId as in source table.

This is simplicited query.

MERGE DocAnalytical daSRC
USING  (
    select 
    da_DaId, da_DocId, da_DelId, 
    FROM DocAnalytical dad
    JOIN DocElems del on dad.da_DelID=del.Del_ID 
) as dest
ON dest.da_DaId=daSRC.da_DaId --i know it's identity but normal its more complex clause
WHEN MATCHED THEN 
UPDATE SET 
da_DocId=dadSRC.da_DocId, da_DelId=dadSRC.da_DelId

WHEN NOT MATCHED THEN 
INSERT (da_DocId, da_DelId, 
VALUES(daSRC.da_DocId, da_DelId, -- daSRC not available
);

Desired result: Before

da_DaID     da_DocId    da_DelId     da_DaId (identity)
----------- ----------- -----------
162         5160        98
163         5160        98
164         5160        99

Not Matched Row

da_DaID     da_DocId    da_DelId
----------- ----------- -----------
300         5300        100

After

da_DaID     da_DocId    da_DelId
----------- ----------- -----------
162         5160        98
163         5160        98
164         5160        99
165         5160        100   << dad_DocId from daSRC (source)
mr R
  • 997
  • 2
  • 12
  • 25
  • da_DaID is incremental, right? da_DocId = 5160 fixed? Can not you use if/else for this purpose? – mkRabbani Jun 27 '19 at 12:06
  • @mkRabbani yes da_DaID (identity) da_DocId fixed I can use CASE but in WHEN NOT MATCHED INSERT i dont get id from source (USING xxx table) – mr R Jun 28 '19 at 19:33

0 Answers0