Solved

Need to duplicate records in SQL server db

Posted on 2014-03-03
2
354 Views
Last Modified: 2014-03-15
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.
0
Comment
Question by:jazz__man
2 Comments
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 500 total points
ID: 39901600
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
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39901779
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?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now