CODEWITHSUNDEEP
sqlselectinsertparent-child
Jojo
Jojo

Reputation: 47

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.

Upvotes: 1

Views: 3778

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You can use two insert select based on pivot table 

insert into aTempTable (OrderItemID, OrderID, Name, Location) 
select a.OrderItemID,  a.OrderID,   a.Name,  b.Location 
from OrderItem as a 
inner join OrderedStock as b on a.OrderID = b.OrderID;


insert into  PostedOrder (OrderID, Location) 
select distinct OrderID, Location 
from aTempTable ;


insert into PostedOrderItem (OrderItemID, OrderID, Name) 
select OrderItemID,  OrderID,   Name
from aTempTable ;

If you need new OrderItemID use this

insert into aTempTable ( OrderID, Name, Location) 
select    a.OrderID,   a.Name,  b.Location 
from OrderItem as a 
inner join OrderedStock as b on a.OrderID = b.OrderID;


insert into  PostedOrder (OrderID, Location) 
select distinct OrderID, Location 
from aTempTable ;


insert into PostedOrderItem ( OrderItemID, OrderID, Name) 
select OrderItemID,  OrderID,   Name
from aTempTable ;

Upvotes: 1

devlin carnate
devlin carnate

Reputation: 8591

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

Upvotes: 1

Related Questions