SQL Merge with inserting into the third table

Question

I want to create a merge that will compare two tables and insert not matched values into another third table or table variable something like this:

MERGE Assets AS target
USING (@id, @name)FROM Sales AS source (id, name) ON (target.id = SOURCE.id)
WHEN MATCHED THEN 
    UPDATE SET target.Status = @status, target.DateModified = SYSUTCDATETIME()
WHEN NOT MATCHED THEN 
    INSERT INTO @tableVar (id, name, status, dateModified)  
    VALUES (@id, @name, @status, SYSUTCDATETIME())

Can this be done or are there other methods?

score 14 · Accepted Answer · answered Feb 22 '12 at 15:46

14

You just cannot do this. MERGE operates on two tables only - source and target.

For your requirement, you need to e.g. use a CTE (Common Table Expression) to find the rows that don't match - and insert those into the third table.

Something like:

;WITH NonMatchedData AS
(
   -- adapt this as needed - just determine which rows match your criteria,
   -- and make sure to return all the columns necessary for the subsequent INSERT
   SELECT (columns)
   FROM dbo.SourceTable
   WHERE ID NOT IN (SELECT DISTINCT ID FROM dbo.TargetTable)
)
INSERT INTO dbo.ThirdTable(Col1, Col2, ....., ColN)
  SELECT Col1, Col2, ....., ColN
  FROM NonMatchedData

answered Feb 22 '12 at 15:46

marc_s

732,580
175
1,330
1,459

1

Is there an Alternative to the `NOT IN` in this case? Something like a `JOIN` that retuns all the rows that failed to `JOIN`? Other then `LEFT JOIN` + `IS NULL`. Also, is the CTE really necessary? Can't he `INSERT INTO ThirdTable FROM SELECT NonMatchedData`? – Alexandre Feb 22 '12 at 15:51
1

@PeekaySwitch: sure - but then you **ARE** doing a `LEFT OUTER JOIN` and checking that a column from the `dbo.TargetTable` is `NULL` .... I don't see what other option would be there, really .... – marc_s Feb 22 '12 at 15:52
1

Do you know on the top of your head what offers better perfomances? I'd expect the `LEFT OUTER JOIN` to be more effective, but maybe they are close together, I just always tried to avoid using `NOT IN` with massive amounts of Data in `Where` (Like IDs) – Alexandre Feb 22 '12 at 15:56
1

@PeekaySwitch: standard answer: *it depends!* - there's no definitive answer. It depends on the size of the tables, the number of rows, what indices you have....... basically, if that kind of performance is a problem/issue for you: try them both, measure them, compare the execution plans - see which one **is** better for you. I cannot say that from here, without knowing your data and your busines .... – marc_s Feb 22 '12 at 15:59
1

Haha, OK! Huge thanks for taking some times to answer some of my questions! I'm still wrapping my head around CTEs and T-SQL in general! It's fun being able to exchange with people that are knowledgable in T-SQL! – Alexandre Feb 22 '12 at 16:02
1

Well, it seems you **can** do this. The other answer worked just fine for me. – Andrew Apr 12 '18 at 01:38

score 12 · Answer 2 · edited Jan 28 '22 at 15:48

12

You CAN do this very easily...

You can wrap the MERGE statement within a INSERT INTO FROM:
http://technet.microsoft.com/en-us/library/bb510625.aspx#sectionToggle2

-OR-

You can do it directly within the merge statement:

Quick example:

WHEN NOT MATCHED THEN
    DELETE
OUTPUT Deleted.* INTO dbo.MyTable;

This will insert the non-matches into your existing destination table. You can use the Updated, Inserted, Deleted v-tables to direct data other places.

edited Jan 28 '22 at 15:48

ΩmegaMan

29,542
12
100
122

answered Dec 24 '12 at 20:05

nm1984

121
1
2

This needs to be in either a `MATCHED` or `NOT MATCHED BY SOURCE` clause, but it does do the trick. – John Eisbrener Jul 24 '17 at 19:53
1

This is the actual answer, and should be upvoted more. So simple and nice. – Andrew Apr 12 '18 at 01:37
Could someone annotate the "quick example" with a little detail of why it works? for instance, is the OUTPUT part of the DELETE, or part of the MERGE? – Be Kind To New Users May 19 '18 at 01:46
@MichaelPotter OUTPUT is part of the MERGE. Take a look at the syntax in the docs. You are looking for https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017#syntax – thebitguru May 28 '18 at 03:31
1

An example for the first version would be nice instead of just a link, especially because this link automatically goes to the newest SQL Server version and not the one OP originally intended to link. – bugybunny Jan 30 '19 at 07:45

SQL Merge with inserting into the third table

2 Answers2