Merge SQL with insert into another table

Question

I want to create a merge that will compare two tables and insert inappropriate 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())

Could you help me transform it using some other constructs.

Answer 1

You just can't do it. MERGEIt works with only two tables - source and target.

For your requirement, you need, for example, use CTE (Common Table Expression) to find rows that do not match, and paste them into the third table.

Sort of:

;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

Answer 2

You can do it very easily ...

MERGE INSERT INTO FROM:
http://technet.microsoft.com/en-us/library/bb510625.aspx#sectionToggle2

-OR -

:

:

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

. , , v-, .