How to use OUTPUT to capture new and old ID?

Question

For example let say I have,

    create table product
    (
      ID INT IDENTITY(1,1) NOT NULL,
      Name VARCHAR(10)
    )

    insert into product(Name) values('a')
    insert into product(Name) values('b')
    insert into product(Name) values('c')
    insert into product(Name) values('d')
    insert into product(Name) values('e')

    create table #tempproduct
    (
      ID INT IDENTITY(1,1) NOT NULL,
      Name VARCHAR(10)
    )

    declare @OutputAttributesValues table
    (
      [NewID] INT, 
      [OldID] INT
    )

    insert into #tempproduct(Name)
    OUTPUT INSERTED.[ID],[ID] INTO @OutputAttributesValues
    select [Name] FROM product

See I need to get both Old and new ID from output. But this sql giving me error "Invalid column name 'ID'." http://sqlfiddle.com/#!3/a27b2/1

score 13 · Accepted Answer · answered Nov 29 '12 at 06:35

13

You can't use traditional INSERT to output non-inserted fields. Use merge instead (caveat: only works with database compatibility >= 100):

MERGE
  #tempproduct AS t
USING(
  SELECT
      Name
      , ID
  FROM
    product) AS s
ON (1=0)  
WHEN NOT MATCHED   
  THEN INSERT (Name) VALUES (Name)
OUTPUT inserted.ID, s.ID INTO @OutputAttributesValues;

SELECT * FROM @OutputAttributesValues

answered Nov 29 '12 at 06:35

YS.

1,808
1
19
33

1

Jamie Thompson had an [excellent article](http://sqlblog.com/blogs/jamie_thomson/archive/2010/01/06/merge-and-output-the-swiss-army-knife-of-t-sql.aspx) on this also. The only issue I've ever had with this method was when there is an INSTEAD OF trigger on the same table. – Mr Moose Nov 29 '12 at 07:17
what is database compatibility >= 100 – Imran Qadir Baksh - Baloch Nov 29 '12 at 08:01
It's for SQL server 2008. I mention that despite you already tag 2008 - because some systems still set the database compatibility to something less than 100 – YS. Nov 29 '12 at 08:17

score 1 · Answer 2 · answered Nov 29 '12 at 06:37

If you want to output both the ID from the Products table as well as the newly created ID INT IDENTITY from the temp table, then you need to extend that temp table to include the Products.ID column as well:

create table #tempproduct
(
   ID INT IDENTITY(1,1) NOT NULL,
   OldID INT,
   Name VARCHAR(10)
)

declare @OutputAttributesValues table
(
  [NewID] INT, 
  [OldID] INT
)

insert into #tempproduct(OldID, Name)
  OUTPUT INSERTED.[ID], Inserted.OldID INTO @OutputAttributesValues
  select ID, [Name] FROM product

How to use OUTPUT to capture new and old ID?

2 Answers2

Linked