I'm using MERGE
to sync data in a table, and I'm seeing some wrong (to me) behavior from SQL Server. When I OUTPUT
the INSERTED.*
values, and a row was deleted, the MERGE
command returns a row with all NULL
columns for each row that was deleted.
For example, take this schema:
CREATE TABLE tbl
(
col1 INT NOT NULL,
col2 INT NOT NULL
);
I do an initial load of data, and all 4 rows are outputted as expected.
WITH data1 AS (
SELECT 1 [col1],1 [col2]
UNION ALL SELECT 2 [col1],2 [col2]
UNION ALL SELECT 3 [col1],3 [col2]
UNION ALL SELECT 4 [col1],4 [col2]
)
MERGE tbl t
USING data1 s
ON t.col1 = s.col1 AND t.col2 = s.col2
WHEN NOT MATCHED BY TARGET
THEN INSERT (col1,col2) VALUES (s.col1,s.col2)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT INSERTED.*;
Now, say I remove 2 rows from the data I'm syncing with the table (in my CTE) and do the same MERGE
, I see 2 rows of all NULL
columns returned.
WITH data1 as (
SELECT 1 [col1],1 [col2]
UNION ALL SELECT 2 [col1],2 [col2]
)
MERGE tbl t
USING data1 s
ON t.col1 = s.col1 AND t.col2 = s.col2
WHEN NOT MATCHED BY TARGET
THEN INSERT (col1,col2) VALUES (s.col1,s.col2)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT INSERTED.*;
To me, this seems like wrong behavior because A) I didn't as for any deleted rows and B) this makes it seem like I inserted these 2 NULL
rows into my table, which I clearly did not. Can anyone shed some light on what's happening?
From the documentation:
output_clause - Returns a row for every row in target_table that is updated, inserted, or deleted, in no particular order. $action can be specified in the output clause. $action is a column of type nvarchar(10) that returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row.
It seems that SQL Server is outputting one row for every row that changed (by an insert or delete). When I specify OUTPUT INSERTED.*
, I'm really only specifying the inserted data, which is null for the 2 rows that were changed. If I specify OUTPUT INSERTED.col1 [InsCol1],INSERTED.col2 [InsCol2],DELETED.col1 [DelCol1],DELETED.col2 [DelCol2],$action
then I can see a better picture of what's happening.
Thanks to Laurence for your comment.