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?