Solved

Need to duplicate records in SQL server db

Posted on 2014-03-03
2
359 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:Scott Pletcher
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 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