Issue with OUTPUT clause

Question

Please refer the below code.

1) How can I insert tias.InventoryID in to @TempIDList. I am getting a syntax error saying tias is undefined.

2) I can avoid the issue in (1) by selecting it using the SELECT statement in the below code. But it opens another issue saying Column Count Mismatch.

How can I do both the tasks at the same time.

Task 1 : Inserting in to [Product].[ProductSupplier] table.

Task 2 : Inserting Tast 1's output in to @TempIDList

INSERT INTO 
[Product].[ProductSupplier](ProductID,SupplierID) 
OUTPUT INSERTED.ID,tias.InventoryID INTO @TempIDList(ProductID,InventoryID)
        SELECT 
            tias.ProductID
            ,tias.FromID
        FROM 
            [Product].[ProductSupplier] ps 
            INNER JOIN @TempInventoryStock tias ON tias.SupplierID = ps.ID 
        WHERE 
            tias.SupplierID <> tias.FromID;

You usually do it by abusing `MERGE` instead, as shown in the linked question. It's an unfortunate restriction on the `OUTPUT` clause for `INSERT` statements. — Damien_The_Unbeliever, Oct 02 '18 at 07:05

score 0 · Answer 1 · answered Oct 02 '18 at 06:53

0

Replace tias in OUTPUT clause:

INSERT INTO 
[Product].[ProductSupplier](ProductID,SupplierID) 
OUTPUT INSERTED.ID,INSERTED.InventoryID INTO @TempIDList(ProductID,InventoryID)
        SELECT 
            tias.ProductID
            ,tias.FromID
        FROM 
            [Product].[ProductSupplier] ps 
            INNER JOIN @TempInventoryStock tias ON tias.SupplierID = ps.ID 
        WHERE 
            tias.SupplierID <> tias.FromID;

answered Oct 02 '18 at 06:53

Denis Rubashkin

2,151
1
9
14

I need to insert the Value from `@TempInventoryStock tias`. Not the value will be inserted to `[Product].[ProductSupplier]` – Harsha W Oct 02 '18 at 06:55

Issue with OUTPUT clause

1 Answers1