SQL Server 使用第一个数据的ID同时插入到2个表的2个不同数据中

0ve6wy6x  于 2022-12-10  发布在  其他
关注(0)|答案(2)|浏览(188)

I have 2 tables: Order and product_order . Every order has some product in it and that's because I store products another table.
Table Order :

Id   name

Table PRODUCT_ORDER :

id   product_id   order_id

Before I start to insert, I don't know what the Order Id is. I want to insert the data into both tables 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 then find the id of the order and than execute the second insert, but I want to do these both to execute at once.

voase2hg

voase2hg1#

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
4nkexdtk

4nkexdtk2#

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]

相关问题