[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

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

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?

Marcus
0
MarcusKoch
Asked:
MarcusKoch
  • 3
  • 2
  • 2
1 Solution
 
tsqlguyCommented:
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.

Thanks
0
 
MarcusKochAuthor Commented:
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

E.g.:
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.

Marcus
0
 
tsqlguyCommented:
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
begin
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

end
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Anthony PerkinsCommented:
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.
0
 
MarcusKochAuthor Commented:
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?

Marcus
0
 
Anthony PerkinsCommented:
Chack out this article that Tim Chapman (aka chapmandew) wrote for a good intro into using the OUTPUT clause:
http://www.builderau.com.au/architect/database/soa/Know-your-DML-The-new-OUTPUT-feature-in-SQL-Server-2005/0,339024547,339253940,00.htm
0
 
MarcusKochAuthor Commented:
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.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now