asked
SQL for duplicating parent and children records
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?
I would suggest taking a look at the [OUTPUT clause][1]. You can create a temporary table that will allow you to output the ID values generated when you INSERT into the table. That way you can join to or read from those values for your additional inserts. There are examples at the link and you can find others online. [1]:
http://msdn.microsoft.com/en-us/library/ms177564.aspx
15 People are following this question.
