SQL for duplicating parent and children records

Question

I'm trying to figure out the best way to create a SQL statement (in SQL Server) to duplicate a parent record and all the children records. I have the below as an example;

-- duplicate the order

insert into Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, 
                    ShippedDate, ShipVia, Freight, ShipName, ShipAddress, 
                    ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
select CustomerID, EmployeeID, getdate(), RequiredDate, null, ShipVia, 
        Freight, ShipName, ShipAddress, ShipCity, ShipRegion, 
        ShipPostalCode, ShipCountry
from Orders
where OrderID = @OrderID

-- find ID of duplicated order
declare @NewOrderID int;
select @NewOrderID = @@IDENTITY;

-- duplicate order details
insert into "Order Details" (OrderID, ProductID, UnitPrice, 
                            Quantity, Discount)
select @NewOrderID, ProductID, UnitPrice, Quantity, Discount
from "Order Details"
where OrderID = @OrderID

This works perfectly for duplicating the child table "Order Details". But I need the ability to then duplicate the children of "Order Details" but see no way of isolating the identity of each of those records and passing to yet another table. Does anyone have any suggestions for how this can be easily accomplished?

look at this: http://stackoverflow.com/questions/6281727/tsql-bulk-insert-data-while-returning-created-ids-to-original-table. Use output id to create child records... — Adam Silenko, Dec 08 '16 at 15:51

score 1 · Answer 1 · answered Jul 31 '13 at 15:18

Do a query to find all of the children records, which presumably have a key that points back to the parent record. You'll hopefully already have an index on that field. Then just use a similar function to do the copy again, except using the new key for the duplicated parent order instead of the old one.

SQL for duplicating parent and children records

1 Answers1