Are there any advantages/disadvantages of making use of a merge over a insert function? Can an insert statement make use of the `OUTPUT'

Note: the ON clause for the merge is set to 1=0 (always insert)

Merge DeviceReading
    Using (
        --table here
    ) As [Source]
    On (1 = 0) -- Effectively Makes this an Insert as 1 Never Matches 0
    When Not Matched Then
        INSERT (
        ) VALUES (
        ) OUTPUT Inserted.[value1], Inserted.[value2] Into @InsertIDTable;

MERGE has more flexible OUTPUT. OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values). INSERT can't do that (for no fundamental reasons; seems to be not implemented).

I can't think of any performance difference. The plans certainly look so similar that I can't make out a difference right now. If there was a major difference SQL Server could optimize one form to the other.

  • the OUTPUT clause can also be used in an INSERT statement. Consequently, I see only disadvantages for using MERGE for unconditional inserts - much more code and unnecessary query plan operators, although probably not significant for performance. – Dan Guzman May 8 '15 at 11:12
  • @DanGuzman the OUTPUT clause cannot refer to the "merge source" in insert statements. – usr May 8 '15 at 11:39
  • In the case of a value assigned by SQL Server, such as an IDENTITY or default constraint value, there is no original value provided for either MERGE...NOT MATCHED or INSERT. One could add a NULL literal to the OUTPUT list for values not specified along with the values specified in the VALUES clause to get before/after values. – Dan Guzman May 8 '15 at 12:25
  • Not sure what you mean. If you use a table as the source for the insert...select you need to match each source row with the ID that was generated. But OUTPUT can't output source values. So it's not possible in general to do that matching. With MERGE you can have a source column that contains some key that can be used to identify rows. – usr May 8 '15 at 13:01

After thinking about this, I decided to answer instead of comment to provide more detailed information.

Yes, an INSERT statement can use an OUTPUT clause. It can be specified before the VALUES clause. See the SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms174335.aspx) for the authoritative T-SQL reference

I suggest you avoid MERGE for unconditional inserts in production code. It provides no additional functionality, requires less codes, and is easier to maintain. Comparing the execution plans of a MERGE versus INSERT, the `MERGE plan incudes additional scalar operators for the extra constants. I wouldn't expect a significant execution time difference but compilation time might be a slightly higher.

Below are functionally identical examples of these two techniques.

DECLARE @value1 varchar(10)
  , @value2 int;
DECLARE @test table ( col1 char(8000), col2 int );
MERGE test
    ( SELECT    1 x
    ) AS [Source]
ON ( 1 = 0 ) -- Effectively Makes this an Insert as 1 Never Matches 0
    INSERT ( [col1], [col2] )
    VALUES ( @value1, @value2 )
  , Inserted.[col2]
    INTO @test;
DECLARE @value1 varchar(10)
  , @value2 int;
DECLARE @test table ( col1 char(8000), col2 int );
INSERT  INTO dbo.test
        ( [col1], [col2] )
OUTPUT  Inserted.[col1], Inserted.[col2]
        INTO @test
VALUES  ( @value1, @value2 );

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.