Ali Raza Ali Raza - 6 months ago 26
SQL Question

How to: select and insert parent child records without using loop in sql

I have two Tables

OrderedStock
---
OrderID Location
1 Richmond
2 Ohio
3 Queens


OrderItem
---
OrderItemID OrderID Name
1 1 Perfume
2 1 Blinds
3 2 Ball
4 3 Cabinet


What is the best approach if I have to copy all the records from these tables to the similar tables(PostedOrder and PostedOrderItem) preserving the correct PostedOrderId in PostedOrderItem table. In actual, I have around 45K rows in Order table to be copied and their nested OrderItem records are around 2 million.

Target Table and its data would look like this

PostedOrdered
---
PostedOrderID Location
11 Richmond
12 Ohio
13 Queens


PostedOrderItem
---
PostedOrderItemID PostedOrderID Name
101 11 Perfume
102 11 Blinds
103 12 Ball
104 13 Cabinet


PostedOrderId and PostedOrderItemId are auto incremented Primary Keys in their respective tables.

Any best approach i should follow without looping thorough with a select satement?

Thanks in advance and apology for the drastic formatting.

Answer

In your Posted Order table, you need to also include an "original id" column. You haven't specified how the new id is to be generated, so I will assume it's an INT IDENTITY(1,1) column.

Example table schema for PostedOrder:

PostedOrderID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Location VARCHAR(100),
OriginalID INT NOT NULL  /* This is the id from the original table  */

Insert records to this table:

INSERT INTO PostedOrder (Location,OriginalID)
SELECT Location, OrderID FROM OrderedStock

You can then use the original id as a link to get the new PostedOrderID for the PostedOrderItem table.

Example table schema for PostedOrderItem:

PostedOrderItemID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
PostedOrderID INT NOT NULL, /* foreign key to PostedOrder table */
Name VARCHAR(100)

Note: If for any reason you need to keep the original id for the items table, you can also add a column for that. It's not necessary for your insert.

Insert records to PostedOrderItem:

INSERT INTO PostedOrderItem (PostedOrderID, Name)
SELECT p.PostedOrderID, o.Name
FROM PostedOrder p
INNER JOIN OrderItem o ON o.OrderID = p.OriginalID
Comments