Insert into 2 different data to 2 table at the same time using first data's id-CodePudding

I have 2 tables; Order and product_order. Every order has some product in it and thats because I store products another table.

TABLE ORDER
Id name

TABLE PRODUCT_ORDER
id product_id order_id

Before I start Insertion I don't know what is Order Id. I want to insert the data both table at once and I need the order id to do that. Both id's are auto incremented. I'm using SQL Server. I can insert first order and than find the id of the order and than execute the second insertion but I want to do these both executions at once.

CodePudding user response：

The output clause is your friend here.

DECLARE @Orders TABLE (OrderID INT IDENTITY, OrderDateUTC DATETIME, CustomerID INT)
DECLARE @OrderItems TABLE (OrderItemID INT IDENTITY, OrderID INT, ProductID INT, Quantity INT, Priority TINYINT)

We'll use these table variables as demo tables with IDs to insert into. You're liking going to be passing the set of items for an order in together, but for the purpose of a demo we'll ad hoc them as a VALUES list.

DECLARE @Output TABLE (OrderID INT)

INSERT INTO @Orders (OrderDateUTC, CustomerID) 
OUTPUT INSERTED.OrderID INTO @Output
VALUES (GETUTCDATE(), 1)

We inserted the Order into the Orders table, and used the OUTPUT clause to cause the inserted (and generated by the engine) into the table variable @Output. We can now use this table however we'd like:

INSERT INTO @OrderItems (OrderID, ProductID, Quantity, Priority) 
SELECT OrderID, ProductID, Quantity, Priority
  FROM (VALUES (5,1,1),(2,1,2),(3,1,3)) AS x(ProductID, Quantity, Priority)
    CROSS APPLY @Output

We cross applied it to our items list, and inseted it as if it was any other row.

DELETE FROM @Output

INSERT INTO @Orders (OrderDateUTC, CustomerID) 
OUTPUT INSERTED.OrderID INTO @Output
VALUES (GETUTCDATE(), 1)

INSERT INTO @OrderItems (OrderID, ProductID, Quantity, Priority) 
SELECT OrderID, ProductID, Quantity, Priority
  FROM (VALUES (1,1,1)) AS x(ProductID, Quantity, Priority)
    CROSS APPLY @Output

Just to demo a little farther here's another insert. (You likely wouldn't need the DELETE normally, but we're still using the same variable here)

Now when we select that data we can see the two separate orders, with their IDs and the products that belong to them:

SELECT *
  FROM @Orders o
    INNER JOIN @OrderItems oi
      ON o.OrderID = oi.OrderID

OrderID OrderDateUTC            CustomerID  OrderItemID OrderID ProductID   Quantity    Priority
------------------------------------------------------------------------------------------------
1       2022-12-08 23:23:21.923 1           1           1       5           1           1
1       2022-12-08 23:23:21.923 1           2           1       2           1           2
1       2022-12-08 23:23:21.923 1           3           1       3           1           3
2       2022-12-08 23:23:21.927 1           4           2       1           1           1

CodePudding user response：

Dale is correct. You cannot insert into multiple tables at once, but if you use a stored procedure to handle your inserts, you can capture the ID and use it in the next insert.

-- table definitions
create table [order]([id] int identity, [name] nvarchar(100))
go
create table [product_order]([id] int identity, [product_id] nvarchar(100), [order_id] int)
go

-- stored procedure to handle inserts
create procedure InsertProductWithOrder(
  @OrderName nvarchar(100),
  @ProductID nvarchar(100))
as
begin
  declare @orderID int
  insert into [order] ([name]) values(@OrderName)
  select @orderID = @@identity
  insert into [product_order]([product_id], [order_id]) values(@ProductID, @orderID)
end
go

-- insert records using the stored procedure
exec InsertProductWithOrder 'Order ONE', 'AAAAA'
exec InsertProductWithOrder 'Order TWO', 'BBBBB'

-- verify the results
select * from [order]
select * from [product_order]