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
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.