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
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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With