1

I get this error, but I thought i declared it already. Can someone tell me where I'm going wrong?

DECLARE @ID TABLE(OrderID INT)

INSERT INTO [dbo].[Order](UserID, To_Adress, OrderDate, TravelTime, ItemCount, Status, TotalPrice) 
OUTPUT INSERTED.OrderID INTO @ID
VALUES (1, 'BIKINI BOTTOM', '20191030 15:00:00', '20191030 15:35:00', 1, 'InTheMaking', 7.50) 

INSERT INTO [dbo].[Order_Product](OrderID, ProductID) VALUES (@ID, 12)

Background: So basically I tried to get the auto-incremented ID that I just inserted and tried to implement the DECLARE OUTPUT method instead of the SCOPE_IDENTITY()-way from the comment from BugFinder. Can I get the ID of the data that I just inserted?

zhrgci
  • 584
  • 1
  • 6
  • 25

1 Answers1

4

Yes, use a FROM clause, because the value is in a table:

DECLARE @IDS TABLE (OrderID INT);

INSERT INTO [dbo].[Order](UserID, To_Adress, OrderDate, TravelTime, ItemCount, Status, TotalPrice) 
    OUTPUT INSERTED.OrderID INTO @IDS
    VALUES (1, 'BIKINI BOTTOM', '20191030 15:00:00', '20191030 15:35:00', 1, 'InTheMaking', 7.50);

INSERT INTO [dbo].[Order_Product](OrderID, ProductID) 
    SELECT i.OrderID, 12
    FROM @IDS i;

I changed the name to @IDS to make it clear that it can have more than one row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Question: why did the second `INSERT` changed to `SELECT` instead of `VALUES` because I still want that OrderID filled in the `[Order_Product]` table – zhrgci Nov 06 '19 at 13:37
  • 1
    @Zheng-rongCai . . . Because `VALUES()` doesn't have a `FROM` clause to refer to `@IDS`. – Gordon Linoff Nov 06 '19 at 13:38
  • Oooohhh, sorry my bad...I didn't find the connection between the insert and select. Damn I've never thought of using `INSERT` and `SELECT` that way :o – zhrgci Nov 06 '19 at 13:42
  • This might not be related but the two ID's in `Order_Product` make together the primary key, so if there were another column `ItemAmount` and it would increment every time this query gets used, how would you do it? Because it's seems almost impossible to do it in one query. – zhrgci Nov 06 '19 at 14:33
  • @Zheng-rongCai . . . You can use `row_number()` in the `select` used for the `insert`. – Gordon Linoff Nov 06 '19 at 18:42