SQL MERGE INSERT INTO not working

Question

I'm having trouble with a MERGE statement in SQL. I tried following guides by Microsoft but with no great success.

I want the code to update any rows it finds are matched and insert the non-matched rows into a different table.

I have been using the code below:

CREATE TABLE dbo.NotMatched
(ID INT , Year INT, Month INT, Quantity INT,   
 CONSTRAINT PK_Matched PRIMARY KEY CLUSTERED (ID));  
GO  

CREATE TABLE dbo.Updated
(ID INT , Year INT, Month INT, Quantity INT,   
 CONSTRAINT PK_Updated PRIMARY KEY CLUSTERED (ID));  
GO  

CREATE TABLE dbo.Staging
(ID INT , Year INT, Month INT, Quantity INT,   
 CONSTRAINT PK_Staging PRIMARY KEY CLUSTERED (ID));  
GO  

MERGE INTO dbo.Updated AS Target  
USING (SELECT ID, Year, Month, Quantity  
        FROM dbo.Staging AS stg) AS Source (ID, Year, Month, Quantity)  
 ON Target.ID = Source.ID  
WHEN MATCHED THEN  
UPDATE SET Target.Year = Source.Year, Target.Month = Source.Month, 
Target.Quantity = Source.Quantity
WHEN NOT MATCHED BY TARGET THEN  
INSERT (ID, Year, Month, Quantity) VALUES (Source.ID, Source.Year, Source.Month, Source.Quantity)  
OUTPUT $action INTO dbo.NotMatched (ID, Year, Month, Quantity);

When I run the code I get the following error:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

I cannot see any obvious errors, if anyone can provide me with some help that would be greatly appreciated.

Regards, Kriss

Squirrel Squirrel · Accepted Answer · 2018-07-12T11:53:33

$action ? what does this do ?

should be

OUTPUT inserted.ID, inserted.Year, inserted.Month, inserted.Quantity
INTO dbo.NotMatched (ID, Year, Month, Quantity);

SQL MERGE INSERT INTO not working

2 Answers