SQL Server 2005 – How to split multiple Insert into... Output Select TOP (z)...From if (Approx) Max number of child rows in a Transaction is known

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 using 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).

Noti开发者_如何学JAVAce 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, but it may 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.

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