I have 3 tables that sore orders, order items and order item comments . I need to copy the data for one order to 3 new tables that have the same structure and maintain the relationship. the order will have a new id for the new table, order items should include this new created id for the order in its values and the comments should also have the id of the newly created order items.
Can this be done in one shot?
Best Answer
Probably the easiest way would be to maintain the old
OrderID
. You can do this even if the new table uses anIDENTITY
column; you can saySET IDENTITY_INSERT dbo.NewOrders ON;
and override. The nextOrderID
generated by the app will be roughly the highest "old"OrderID + 1
(keeping in mind thatIDENTITY
does not guarantee a contiguous sequence for a variety of reasons).However if the goal is to renumber the orders (not sure why you would want to do that), the easiest way would be to temporarily add a column mapping to the old
OrderID
, using that to populate the related tables, then dropping it. Something like:Once you're happy that the data is all mapped correctly:
There isn't a way to do all of this in one statement. You may be able to do it in two with a really complicated and over-engineered
MERGE
statement and theOUTPUT
clause, but I highly recommend against that for other reasons.