October 19, 2010 at 2:50 pm
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.
October 19, 2010 at 3:10 pm
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
October 20, 2010 at 5:57 am
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.
October 20, 2010 at 6:02 am
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
October 20, 2010 at 6:04 am
Try using MERGE, you can with that.
October 20, 2010 at 6:32 am
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.
October 20, 2010 at 12:26 pm
OK, I got the MERGE (WHEN NOT MATCHED THEN... INSERT) with OUTPUT to work. Thanks for the link Dave!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy