0

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?

Sanders
  • 438
  • 3
  • 13
  • use `$action` in the output clause to distinguish. – Laurence Sep 11 '14 at 21:15
  • @Laurence Right, but the question is why SQL Server is outputting deletes in the first place when I'm only specifying inserts. – Sanders Sep 11 '14 at 21:20
  • 1
    From the manual "output_clause: Returns a row for every row in target_table that is updated, inserted, or deleted, in no particular order". It's not very intuitive, but it does what it says. http://msdn.microsoft.com/en-us/library/bb510625.aspx. To see why, consider you can, for example, say `OUTPUT deleted.*, $action, inserted.col1 as x, inserted.col2 as y` – Laurence Sep 11 '14 at 21:25
  • @Laurence So they're essentially outputting one row for every change (ins/up/del) and since I'm only selecting the inserted columns they're null. You're right, it makes sense, but it's not very intuitive. – Sanders Sep 11 '14 at 21:43

1 Answers1

0

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.

Sanders
  • 438
  • 3
  • 13