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?

Use the OUTPUT clause as Gidil suggested. The OUTPUT functionality is made for this kind of processing. — Adam Haines, Jul 31 '13 at 15:45

Sonam · Answer 1 · 2013-07-31T15:16:47.800

0

To find out the latest identity insert please try :

SELECT @NewOrderID=IDENT_CURRENT('tablename')

edited Jul 31 '13 at 15:16

answered Jul 31 '13 at 15:09

Sonam

3,406
1
12
24

score 0 · Answer 2 · answered Jul 31 '13 at 15:17

0

Have you considered using the OUTPUT clause?
I can add an example if you're interested

answered Jul 31 '13 at 15:17

Gidil

4,137
2
34
50

SQL for duplicating parent and children records

2 Answers2