SQL Server: Is it possible to copy tables with relationshsips into new tables while creating a new identity key

Posted on 2009-12-29
Last Modified: 2012-05-08
To simplify my problem lets say I have two tables Order and OrderDetail that have relationships using the primary key, which is a identity field. The orderID is a forgein key field in OrderDetail. I need to copy the whole set of tables in another set of tables that have the same fields. In the new  table set I need to create a new identity key for each order. The OrderDetail needs to have the new key in its foreign key field.  Is there a way to accomplish this solely in a stored procedure in SQL server.
I know I could do a loop in my app code over the orders and then add a Order record and the corresponding OrderDetails per stored proceduree, but I would like to keep this logic on the server. Maybe it works with a cursor, but I don't have enough experience with those. Any ideas?

Question by:MarcusKoch
    LVL 3

    Expert Comment

    Can you give an example of data

    so if your order table is

    orderid  ordername etc etc
    1            juice
    2           water
    3           soda

    and your order detail table is

    orderdetailid  orderid  description
    123                1            orange juice
    246                2            distilled
    369                3            grape fizzy soda

    what would the two new tables look like and I will write you a query.


    Author Comment

    Here is an example. I added the third table which is closer to my example. My work is a order system for a dance camp and one orderer can order classes for a group of people. This is why the customer table is connected to the OrderDetails.

    TempOrders Table:
    TempOrderid  ODate
    4                1.1.09
    5                4.1.09
    6                5.1.09

    TempOrderDetails Table:
    orderdetailid  orderid  TempCustomerID  description
    387               4            10                        Class 1
    388               4            11                        Class 2
    389               5            12                        Class 1
    390               5            13                        Class 1
    391               5            14                        Class 1
    392               5            15                        Class 3
    393               6            16                        Class 3

    TempCustomers Table:
    TempCustomerID Name
    10                        Smith
    11                        Pit
    12                        Williams
    13                        Heiney
    14                        DelMar
    15                        Stevens
    16                        Phillips

    Now I need to copy one TempOrders record (where TempOID= @TempOID) plus the TempOrderDetails and the TempCustomers records in the regular tables:
    Orders, OrderDetails, Customers

    Table Orders:
    orderid  ODate
    1           11.12.08
    2           14.12.08

    TempOrderDetails Table:
    orderdetailid  orderid  TempCustomerID  description
    201               1            1                        Class 1
    202               2            2                        Class 2
    203               2            3                        Class 2

    So the inserted order record needs to get an orderid of 3, the first orderdetail an orderdetailid of 204, the new customer a customerid of 4, etc.

    Is this doable in a stored procedure?

    For an AdminMode I would need to update the records in the final tables with the records of the temp tables. In Admin Mode the final table records of an order are copied into the temp tables first . The temp tables hold also the orginal IDs to be able to update the correct records. Is there a way to do even this in a stored procedure. If I need to use a cursor it might still be better than doing a stored procedure call from the client browser for each row in the TempOrderDetails table. For performance conciderations: The avarage order has only two OrderDetails for 2 people, the avarage is between 2 and 4 and the maximum is about 20 in one order.

    LVL 3

    Expert Comment

    Yes it's doable in a sproc but you're probably going to have to do it in a loop unfortunately. There really isn't an elegant way to do what you're trying to do.

    :-( Sorry I can't be of more help, but there is not a great way to move from the temp tables to a static table while keeping the existing relationships in tact.

    To do a basic sql loop here is the syntax

    declare @count int
    select @count = count(tempcustomerID) from tempcustomer
    while @count <> 0
    declare @tempcustomerID INT
    SELECT @tempcustomerID = top 1 tempcustomerID FROM TempCustomer

    INSERT Customer (Fields Here)
    SELECT @tempcustomerID, etc. etc.

    DECLARE @NewCustomerID INT
    SELECT @NewCustomerID = Scope_Identity() FROM Customer

    (So on and so forth for each of the fields, then at the end of moving each individual order set just delete the info and move forward.)

    DELETE TempCustomer WHERE TempCustomerID = @TempCustomerID

    LVL 75

    Expert Comment

    by:Anthony Perkins
    You should be able to do this by
    1 Doing an INSERT into the Orders table using the OUTPUT clause
    2. Use the IDENTITY values in the INSERTED table from the previous INSERT to add into the OrderDetails table.

    Author Comment

    Acperkins, I have an idea what you you mean, but could you give me an example? What do you mean exactly with the OUTPUT clause?

    LVL 75

    Accepted Solution

    Chack out this article that Tim Chapman (aka chapmandew) wrote for a good intro into using the OUTPUT clause:,339024547,339253940,00.htm

    Author Closing Comment

    Thanks for the tip with the Output clause. It solved my problem. I had to add the tempkey in the final table to achive the result, but it worked without using any cursors or loops. Thank.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
    by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 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

    19 Experts available now in Live!

    Get 1:1 Help Now