Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need to duplicate records in SQL server db

Posted on 2014-03-03
2
Medium Priority
?
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 2000 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 70

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

670 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