I started working on a project that already has some scripts running and while I was looking some of the scripts that have already been done I encountered an scenario that looks something like this.
DROP TABLE IF EXISTS #Persons SELECT PersonId = 1, Firstname = 'Joaquin', LastName = 'Alvarez' INTO #Persons DECLARE @inserted table (PersonId INT, Firstname VARCHAR(50), Lastname VARCHAR(50)) MERGE INTO dbo.Persons P USING #Persons TP ON 1 = 0 -- Forcing mismatch WHEN NOT MATCHED THEN INSERT ( PersonId, Firstname, Lastname ) VALUES ( TP.PersonId, TP.Firstname, TP.LastName ) OUTPUT INSERTED.PersonId, INSERTED.Firstname, INSERTED.LastName INTO @inserted;
My Question here is why they would use the merge into and force the mismatch just to perform an insert, they could have done the same without it with something like this.
DROP TABLE IF EXISTS #Persons SELECT PersonId = 1, Firstname = 'Joaquin', LastName = 'Alvarez' INTO #Persons DECLARE @inserted table (PersonId INT, Firstname VARCHAR(50), Lastname VARCHAR(50)) INSERT INTO Persons OUTPUT INSERTED.PersonId, INSERTED.Firstname, INSERTED.LastName INTO @inserted VALUES (1, 'Joaquin', 'Alvarez')
The first option is faster than the last one? or they’re bot the same? This is the first time I see the merge into used this way.
Advertisement
Answer
You would really need to ask the person who wrote the code. But I can think of two and a half reasons.
The first is that the original code was both an INSERT
and UPDATE
so the author used MERGE
to handle the code. As the code was tested or as requirements changed, the person who wrote it realized that the UPDATE
was not needed, but left the MERGE
.
The half reason is that someone wrote the code expecting UPDATE
s to be needed over time, so tried to future-proof the code.
The second reason is that the author may simply prefer MERGE
s over UPDATE
s and INSERT
s because it is one statement that is more powerful than either of those individually. So, they simply always use MERGE
.