Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 292
Imran Qadir Baksh - Baloch Avatar asked Nov 29 '12 06:11

Imran Qadir Baksh - Baloch


2 Answers

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
like image 89
YS. Avatar answered Oct 28 '22 15:10

YS.


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 
like image 1
marc_s Avatar answered Oct 28 '22 15:10

marc_s