Why does upserted INSERTED.ID and DELETED.ID both contain values after MERGE?
Surprisingly, I cannot find an answer to this on Google, although my terminology may be off. I also haven't seen an explanation from MDSN .
Take the following code to accomplish a simple one MERGE
:
DECLARE @tbl_1 TABLE (ID int IDENTITY(1,1), person nvarchar(20));
DECLARE @tbl_2 TABLE (ID int IDENTITY(1,1), person nvarchar(20));
INSERT INTO @tbl_1 (person) VALUES ('Bob'),('Ted'),('Brian');
INSERT INTO @tbl_2 (person) VALUES ('Bob'),('Ted'),('Peter');
MERGE INTO
@tbl_2 as tgt
USING
@tbl_1 as src
ON
(tgt.person = src.person)
WHEN MATCHED THEN
UPDATE SET tgt.person = src.person
WHEN NOT MATCHED BY TARGET THEN
INSERT (person) VALUES (src.person)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$ACTION,
DELETED.ID,
DELETED.person AS PersonOld,
INSERTED.ID,
INSERTED.person AS PersonNew;
In the results, I see that the ID value of each row is shown for both rows INSERTED
and DELETED
where holds UPDATE
:
Why is this please? I expect what DELETED.ID
will happen NULL
after the update, with INSERTED.ID
representing the UPSERTED row (I've worked with triggers in the past and the intended one MERGE
will follow the same approach).
source to share
Because what MERGE
you think is UPSERT
(update + insert).
The tables now INSERTED
record information about the rows added by the INSERT
and commands UPDATE
, and the table DELETED
contains information about the rows that were either updated or deleted.
Take a look at the MSDN documentation on how to use "inserted and deleted tables" :
Table inserted stores copies of rows affected during the INSERT and UPDATE. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. the rows in the inserted table are copies of the new rows in the trigger table.
and
An update operation is similar to a delete operation followed by an insert operation; the old rows are first copied to the remote table, and then the new rows are copied to the trigger table and the inserted table.
Update:
I saw you commenting on your question that you figured out that the operation is actually a delsert under the hood. And you might be thinking why that would be?
Think about how data is stored in SQL Server. It is stored in 8KB pages and when you update the information in a column that is contained in the data page, the entire data page is rewritten, so essentially delsert .
And the same with INSERT
, a new line will go on the data page (and may generate page splitting, but this is a different object) and that the entire data page needs to be rewritten.
source to share