none
How do I assign INSERTED.Id to an input/output parameter in MERGE statement?

    Question

  • I'm using a MERGE statement in a stored procedure and one of the parameters is @Id which is an input/output parameter.

    I know how to get the Id of a newly inserted row in a MERGE statement. My question is: do I need to save that Id in a temp table first then assign its value to my parameter or is there a direct way to assign it to my parameter?

    CREATE PROCEDURE myProc
      @Id INT OUTPUT,
      @SomeData VARCHAR(50)
    AS
      MERGE myTable t
      USING (SELECT @Id AS Id, @SomeData AS SomeData) s
      ON (t.Id = s.Id)
      WHEN MATCHED THEN
        UPDATE
        SET SomeData = s.SomeData
      WHEN NOT MATCHED THEN
        INSERT
          (SomeDataField)
        VALUES
          (s.SomeData)
        OUTPUT INSERTED.Id;
     

    Thanks, Sam


    • Edited by imsam67 Monday, September 30, 2013 12:28 AM
    Monday, September 30, 2013 12:27 AM

Answers

  • I know how to get the Id of a newly inserted row in a MERGE statement. My question is: do I need to save that Id in a temp table first then assign its value to my parameter or is there a direct way to assign it to my parameter?

    Multiple rows may be affected by a MERGE so you'll need to insert the desired value(s) using the OUTPUT clause into a temp table or table variable. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by imsam67 Monday, September 30, 2013 12:53 AM
    Monday, September 30, 2013 12:52 AM

All replies

  • I know how to get the Id of a newly inserted row in a MERGE statement. My question is: do I need to save that Id in a temp table first then assign its value to my parameter or is there a direct way to assign it to my parameter?

    Multiple rows may be affected by a MERGE so you'll need to insert the desired value(s) using the OUTPUT clause into a temp table or table variable. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by imsam67 Monday, September 30, 2013 12:53 AM
    Monday, September 30, 2013 12:52 AM
  • Thanks Dan!

    Thanks, Sam

    Monday, September 30, 2013 12:53 AM