I have these staging tables:
- Order (PK=OrderID),
- SubOrder(PK=SubOrderID, FK=OrderID) and
- Item(PK=ItemID, FK1=SubOrderID, FK2=OrderID).
I established relationships on the client (C#.NET and copied tables to staging tables in SQL Server suing SQLBulCopy).
Now I need to establish Parent/Child/Grand-Child relationships on the server. I have scripts that can do that (I am using OUTPUT statements along with Insert statements and I output PKs to temporary tables which I later use to insert child rows). Notice that initially I had Foreign key relationships between Grand-child and parent (Item and Order) established on the client. The SubOrder is introduced as a quantity limit (Imagine that as a Maximum items that can fit into Shipment box. All items are of the same size – in my case Item rows are of the same size)
The main problem: I can have tens of thousands of Items to insert into Production tables, let’s call them: OrderP, SubOrderP and ItemP . I also dynamically generate temp tables: OrderPWithRealPK and SubOrderPWithRealPK
which hold just inserted Parent PKs.
I can have as little as 1 Order, 1 Suborder and 1 Item and many times like that or 1 Order, 10 Suborder and in each Suborder up to 100 Item elements (so the distribution of (n) Order , (m) SubOrder and (k) Item elements is not predictable.
In the table below I have these parameters:
N=7 number of Order-s
M=14 number of SubOrder-s
K=23 number of Item-s
L=2 max number of Items in an Suborder
J= Approx. number of items to be inserted in a transaction. (but items that are included need to belong to the same Order(possibly it would be OK to be together in the same SubOrder)
P=No. of Items in the largest Order. (this can drive what (J) number can be, but only if we have larger Order-s). If we have many small Order-s then J can be predetermined. (In our example about 10)
Given (K) number of Items I would like to create relatively equal buckets of elements that can be inserted at once in a transaction , but to be submitted along with their parents and preferably Grand-parents.
Right now I have a manual transaction where I first insert a special field with ‘TR’ value (representing ‘In Transaction’) and do the insert and do an Update with ‘00’ to that field to denote all the Items belonging to an Order are Inserted and other processes with query that special field for the value ‘00’ . It would be good If I can avoid this. I think it would be OK to have transactional scope to SubOrder Level if doing automatic transaction (with Begin Trans/End Trans)
If I have a table below let’s say that I would like to have items with these orders to go together when being saved into Item table (of course Item PK will be generated with OUTPUT clause): - 1, 3, 4, and 5 (9 Items) - 2 (9 items) - 6,7 (4 itmes)
The orders can be inserted in any order and preferably Suborder and Items elements need to be inserted in the order in which they were created. Imagine that I would use a While loop and TOP (Z) and a proper join Query to select Items (Grand-Child belonging to Parent-s and associated Child elements) To be inserted in a transaction.
Thanks. Rad
SeqNo OrderID SubOrder ItemID No. of Items ----------------------------------------------------------------------------------- 01 1 1 100 2 02 1 1 101 ==================================================== 03 2 2 201 9 04 2 2 202 05 2 3 301 06 2 3 302 07 2 4 401 08 2 4 402 09 2 5 501 10 2 5 502 11 2 6 503 =================================================== 12 3 7 601 2 13 3 7 602 =================================================== 14 4 8 801 1 =================================================== 15 5 9 901 5 16 5 9 902 17 5 10 1001 18 5 10 1002 19 5 11 1201 ================================================== 20 6 12 1201 1 ================================================== 21 7 13 1301 3 22 7 13 1302 23 7 14 1401