Need to duplicate records in SQL server db

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.
Who is Participating?
UnifiedISConnect With a Mentor Commented:
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

INSERT a duplicate of the customer record

'retrieve the newly created customer ID

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

Scott PletcherSenior DBACommented:
Are you:
A) just cloning the orders, the customer id stays the same, like some type of recurring order?
B) cloning the customer id and orders?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.