March 12, 2015 at 5:34 am
I'm not even sure this is possible but I'm using MERGE in a process that has 3 source tables (the process steps through each source table sequentially) and I need to delete from the Target database occasionally.
My current code is
sqlMerge = "MERGE " + TableName + " AS target USING @CData AS source" +
" ON target.TotRsp = source.TotRsp AND target.ClientRef = source.ClientRef AND target.dbPatID = source.dbPatID" +
" WHEN MATCHED THEN" +
" UPDATE SET dbPatFirstName = source.dbPatFirstName, dbPatLastName = source.dbPatLastName, Tot30d = source.Tot30d," +
" Tot60d = source.Tot60d, Tot90d = source.Tot90d, " +
" TotDue = source.TotDue, TotTot = source.TotTot," +
" LastPayDate = source.LastPayDate, LastPayAmt = source.LastPayAmt," +
" dbDocID = source.dbDocID" +
" WHEN NOT MATCHED THEN" +
" INSERT (dbPatID, dbPatFirstName, dbPatLastName, Tot30d, Tot60d, Tot90d, " +
" TotDue, TotTot, LastPayDate, LastPayAmt, dbDocID, TotRsp, ClientRef)" +
" VALUES (dbPatID, dbPatFirstName, dbPatLastName, Tot30d, Tot60d, Tot90d, " +
" TotDue, TotTot, LastPayDate, LastPayAmt, dbDocID, TotRsp, ClientRef);";
// " WHEN NOT MATCHED BY SOURCE THEN " +
//" DELETE
The Target db data is made up from several different clients and when the MERGE runs it uses TotRsp, ClientRef and dbPatID to uniquely match a source row to the target row and if no match it inserts the source row.
My problem is that when this runs with Source A first, it will delete all merged data from Source B & C. Then when Source B runs it will insert all Source B data but delete all from A & C and so on.
Is there way that that I can include additional clauses into NOT MATCHED BY SOURCE THEN so it knows only to delete when data has come from say Source A. Once the data is in the target table there is no reference to which source table it came from tho.
If there isn't a solution I suppose I could always add an extra column to the target db to indicate which source it came from and then have something like
NOT MATCHED BY SOURCE AND t.Source = 'SourceA'.
That's quite a bit of work my end to do that tho so I'd like to be sure it works.
thanks,
March 12, 2015 at 8:36 am
try splitting the update/delete steps out of the MERGE and do them separately.
Note that MERGE has some problems (some of them severe). Also note that MERGE is not an atomic operation.
Gerald Britton, Pluralsight courses
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply