Skip to content
Advertisement

Difference between performing INSERT INTO vs Using MERGE INTO

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 UPDATEs to be needed over time, so tried to future-proof the code.

The second reason is that the author may simply prefer MERGEs over UPDATEs and INSERTs because it is one statement that is more powerful than either of those individually. So, they simply always use MERGE.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement