Copy related records from 3 tables to another 3 tables and maintain the relationship between them

Question

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?

Answer 1

Probably the easiest way would be to maintain the old OrderID. You can do this even if the new table uses an IDENTITY column; you can say SET IDENTITY_INSERT dbo.NewOrders ON; and override. The next OrderID generated by the app will be roughly the highest "old" OrderID + 1 (keeping in mind that IDENTITY 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:

ALTER TABLE dbo.NewOrders ADD OldOrderID INT;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT dbo.NewOrders(OrderDate, CustomerID, etc., OldOrderID)
  SELECT OrderDate, CustomerID, etc., OrderID
  FROM dbo.OldOrders;

INSERT dbo.NewOrderItems(OrderID, ProductID, Quantity, etc.)
  SELECT n.OrderID, o.ProductID, o.Quantity, etc.
    FROM dbo.NewOrders AS n
    INNER JOIN dbo.OldOrderItems AS o
    ON n.OldOrderID = o.OrderID;

INSERT dbo.NewOrderComments(OrderID, CustomerID, etc.)
  SELECT n.OrderID, o.CustomerID, etc.
    FROM dbo.NewOrders AS n
    INNER JOIN dbo.OldOrderComments AS o
    ON n.OldOrderID = o.OrderID;

COMMIT TRANSACTION;

Once you're happy that the data is all mapped correctly:

ALTER TABLE dbo.NewOrders DROP COLUMN OldOrderID;

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 the OUTPUT clause, but I highly recommend against that for other reasons.

Answer 2

The solution in https://dba.stackexchange.com/a/80959/52708 is easy but if the tables are large, adding and dropping a column can be expensive. So this may be a case where a stored procedure works better and faster. The first loop iterates over the neworders table, then within that iterate over the neworderitems that match the neworders and finally iterate over the newordercomments table.