x

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?

more ▼

asked Jul 31, 2013 at 03:03 PM in Default

avatar image

reamades
71 2 4 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I would suggest taking a look at the OUTPUT clause. 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.

more ▼

answered Aug 01, 2013 at 09:36 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x445
x438

asked: Jul 31, 2013 at 03:03 PM

Seen: 631 times

Last Updated: Aug 01, 2013 at 09:36 AM

Copyright 2017 Redgate Software. Privacy Policy