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.
2条答案
按热度按时间voase2hg1#
The output clause is your friend here.
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.
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:
We cross applied it to our items list, and inseted it as if it was any other row.
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:
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.