Take the 2-minute tour ×

I've been using the MERGE operation in SQL Server 2008, but have now come across a similar scenario where I need the same "detection" behaviour (WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, WHEN NOT MATCHED BY TARGET), but want the resulting operations to be on different tables, not the source or the target.

In other words, I have two result-sets, beforeTable and afterTable and I want to do something along the lines of:

MERGE afterTable AS dest
USING beforeTable AS source 
ON (dest.ID = source.ID)
    --INSERT tblModifiedItems (ID) VALUES (ID)
    --INSERT tblRemovedItems (ID) VALUES (ID)
    --INSERT tblAddedItems (ID) VALUES (ID)

Presently I do this with three seperate (albeit very simple) queries across the two tables, but the MERGE approach would allow it to be done as a single operation. I just can't see whether it can be used to target different tables than the two under comparison.

Is this possible, and if so how?


The reason I want to do this is not due to issues around atomicity of the INSERTS into added/removed/modifed (the whole thing is already in a transaction). It is because I want to be able to generate the three sets of data in a single query, or at least a single statement which also makes the intent very clear.

share|improve this question
MERGE, as with the other DML ops (INSERT, UPDATE, DELETE) can only affect one table (although other tables can be used for obtaining values, filtering, etc) –  Damien_The_Unbeliever Mar 16 '12 at 10:48

1 Answer 1

up vote 2 down vote accepted

short answer would be: no, you can't. MERGE is an operation between source table and destination table and that's it. But, I suggest an work-around (there is always one). You'll have to write some more code, but it will do the trick.

Do this:

DECLARE @AuxTable table(    
    id int,
    OLD_name varchar(50),
    new_name varchar(50)

merge table2 as dest
using table1 as src
on (dest.id=src.id)
when not matched by target then
  insert (id, name) values (src.id, src.name)
when matched then
  UPDATE SET dest.NAME=src.name
when not matched by SOURCE then

  output inserted.id, DELETED.name, INSERTED.NAME
  into @AuxTable

  select * from @AuxTable

see the "output into @AuxTable"? That will insert all the modified values by your merge on a temp table. What yo'll have to do is run through this table and build a string to execute the SQL on your other table.

Use this logic:

share|improve this answer
From what I can see - this won't actually solve the problem. Unless I've misunderstood it, "output" can only appear at the end of the whole MERGE statement (not in the individual cases) and will write all the records out to a third table, but won't let me mark the records as to whether they were matched, not matched in source, etc. So it doesn't get me any further - unless I've misunderstood the usage of output. –  Rob Levine Mar 16 '12 at 10:11
of course it does. Look at my edit :) –  Diego Mar 16 '12 at 10:28
OK - I see what you mean - although I'd need to have a column that was not nullable. Next part of the question on your solution. I don't actually want to do any operations in the matched/not matched/etc cases other than to build up this list of added/remove/modified. I don't really want to amend the destination table at all - just build up a summary of changes between src and dest. Can I use output like you have indicated, without actual operations on the dest table? –  Rob Levine Mar 16 '12 at 10:35
sure, just add a begin transaction before the query and a rollback in the end. Table Variables don't participate on transactions so you will have the result of what "would have happened" in your Auxtable, without that actually happening. Talking of work-arounds... :) –  Diego Mar 16 '12 at 10:43
The trouble then is that I am actually doing the work of the inserts, updates and deletes - only to then roll it back. I think your solution is a pretty good creative set of workarounds, but overall I suspect I am trying to misuse the MERGE command a bit too much! –  Rob Levine Mar 16 '12 at 10:50

Your Answer


