Using another column besides INSERTED.* in the OUTPUT clause for an insert

Question

Using another column besides INSERTED.* in the OUTPUT clause for an insert

Victor Kirkpatrick

Hall of Fame

Points: 3672
More actions
October 19, 2010 at 2:50 pm

#230642

I am using the OUTPUT clause in an INSERT statement, where I am trying to get a list of identity generated IDs, but yet also retrieve the original IDs as well so that I can match them up for some inserts for another table later. The actual code is more complex, but this simple example shows my problem:
CREATE TABLE dbo.DrumBrands(
Brand_IDINT IDENTITY(1,1) NOT NULL,
BrandNameVARCHAR(50) NOT NULL
)
INSERT dbo.DrumBrands ( BrandName ) VALUES ( 'Ludwig' ), ( 'Pearl' ), ( 'Tama' )
SELECT * FROM DrumBrands
INSERTdbo.DrumBrands ( BrandName )
OUTPUTINSERTED.Brand_ID AS NewBrand_ID
,DB.Brand_ID AS CopiedBrand_ID
SELECT'DW' AS BrandName
FROMdbo.DrumBrands DB
WHEREBrand_ID = 1
This returns the following error:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "DB.Brand_ID" could not be bound.
Question is, why can't I seem to reference DB.Brand_ID in the OUTPUT clause? Is is illegal to reference non-INSERTED table columns? If you comment out that line it works fine. Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

yeah i think you'll want to insert the output into a table variable, then join it to the original table to get everything you need:

DECLARE @myTableVariable TABLE(NewBrand_ID int,BrandName varchar(30) )

INSERT dbo.DrumBrands ( BrandName )

OUTPUT

INSERTED.Brand_ID AS NewBrand_ID,

INSERTED.BrandName into @myTableVariable

INTO @myTableVariable

SELECT 'DW' AS BrandName

FROM dbo.DrumBrands DB

WHERE Brand_ID = 1

SELECT

DB.Brand_ID ,

x.NewBrand_ID,

x.BrandName

FROM dbo.DrumBrands DB

INNER JOIN @myTableVariable x

ON DB.BrandName = x.BrandName

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Victor Kirkpatrick Hall of Fame Points: 3672 More actions · Answer 2

Thanks Lowell, but that depends on BrandName being unique, which it may not be (in the real-world case). Back to my original question: can you use a column from one of the regular tables (not the INSERTED one) in the OUTPUT clause? In the end, what I need here is to match each new Brand_ID identity that was created to its original Brand_ID.

Lowell SSC Guru Points: 323518 More actions · Answer 3

no, you can't; the OUTPUT variable has access to the INSERTED and DELETED tables only;

so you need to pass in your original Id for example, so it could be part of the INSERTED table.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Try using MERGE, you can with that.

http://sqlblog.com/blogs/jamie_thomson/archive/2010/01/06/merge-and-output-the-swiss-army-knife-of-t-sql.aspx

Clear Sky SQL
My Blog[/url]

Victor Kirkpatrick Hall of Fame Points: 3672 More actions · Answer 5

That's interesting that you can't reference a non-INSERTED table column when inserting, because you CAN when deleting. Here is an example from books online OUTPUT clause:

USE AdventureWorks2008;

GO

DECLARE @MyTableVar table (

ProductID int NOT NULL,

ProductName nvarchar(50)NOT NULL,

ProductModelID int NOT NULL,

PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto

OUTPUT DELETED.ProductID,

p.Name,

p.ProductModelID,

DELETED.ProductPhotoID

INTO @MyTableVar

FROM Production.ProductProductPhoto AS ph

JOIN Production.Product as p

ON ph.ProductID = p.ProductID

WHERE p.ProductID BETWEEN 800 and 810;

--Display the results of the table variable.

SELECT ProductID, ProductName, PhotoID, ProductModelID

FROM @MyTableVar;

GO

Notice the reference to p.Name and p.ProductModelID! I just don't get the difference and why you can't when inserting but you can when deleting. Arrrgghhh!

OK Dave, I will give the MERGE thing a try. Thanks to both of you for the replies.

Victor Kirkpatrick Hall of Fame Points: 3672 More actions · Answer 6

OK, I got the MERGE (WHEN NOT MATCHED THEN... INSERT) with OUTPUT to work. Thanks for the link Dave!