Need to duplicate records in SQL server db

jazz__man
jazz__man used Ask the Experts™
on
Hi
Say I have table a which generates CustomerID for every inserted record and then the customer has 5 orders linked in the orders table by CustomerId, and generates an OrderID for each entry. Now say I have a dispatched table that every order is linked to by OrderID. What would be the SQL code to duplicate a complete set of linked records obviously with the new Ids being created due to being inserted?

I hope this makes sense.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can use SCOPE_IDENTITY to retrieve the new ids from your inserts and then use those new ids to retrieve and insert the duplicate records. SCOPE_IDENTITY returns the value from the most recent insert so you need to include all your inserts in one transaction.

DECLARE @NewCustomerID int

'use the begin/end to keep everything within the same scopee
BEGIN

INSERT a duplicate of the customer record

'retrieve the newly created customer ID
SELECT @NewCustomerID = SCOPE_IDENTITY()

'new order records using the @NewCustomerID
INSERT ID, original order columns
SELECT @NewCustomerID, Original order columns

END
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Are you:
A) just cloning the orders, the customer id stays the same, like some type of recurring order?
OR
B) cloning the customer id and orders?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial