How to use OUTPUT to capture new and old ID?

问题:

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

回答1:

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

回答2:

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