SQL Server 2005 –如何拆分多个 Insert into...Output Select TOP (z)...From if (Approx) 事务中的最大子行数已知
我有这些临时表：
- 订单（PK=OrderID）、
- 子订单（PK=SubOrderID、FK=OrderID）和
- 项目（PK=ItemID、FK1=SubOrderID、FK2=OrderID）。
我在客户端上建立了关系（C#.NET 并使用 SQLBulCopy 将表复制到 SQL Server 中的临时表）。
现在我需要在服务器上建立父/子/孙子关系。
我有可以做到这一点的脚本（我使用 OUTPUT 语句和 Insert 语句，并将 PK 输出到临时表，稍后使用 插入子行）。
请注意，最初我在客户端上建立了孙子和父级（项目和订单）之间的外键关系。
引入子订单作为数量限制（想象一下，作为可以放入装运箱的最大物品。所有物品的尺寸相同 - 在我的例子中，物品行的尺寸相同。）
主要问题：我可以有数十个要插入到生产表中的数千个项目，我们称它们为：OrderP、SubOrderP 和 ItemP。我还动态生成临时表：OrderPWithRealPK 和 SubOrderPWithRealPK，它们保存刚刚插入的父 PK。
我可以有少至 1 个订单、1 个子订单和 1 个项目，并且多次这样或 1 个订单、10 个子订单，并且每个子订单中最多有 100 个项目元素（因此 (n) Order 、 (m) SubOrder 和 ( k) 项目元素是不可预测的，
在下表中，我有以下参数：
- N=7 订单数量
- M=14 子订单数量
- K=23 项目数量
- L=2 最大项目数量子订单
- J= 交易中插入的大约商品数量（但包含的商品必须属于同一订单，但可以放在同一子订单中）
P= 交易中的商品数量。最大订单（这可以确定 J 数，但前提是我们有较大的订单）
如果我们有许多小订单，则可以预先确定 J（在我们的示例中）。 10）
给定（K）个项目，我想创建相对相等的元素桶，这些元素可以立即插入到事务中，但要与它们的父母（最好是祖父母）一起提交
现在我有一个手动交易 。我首先插入一个带有“TR”值的特殊字段（代表“In Transaction”），然后执行插入操作，并使用“00”对该字段进行更新 表示属于订单的所有项目均已插入，并且其他进程查询该特殊字段的值 '00' 。 如果我能避免这种情况就好了。我认为如果进行自动交易（使用Begin Trans/End Trans），那么将交易范围设置为子订单级别就可以了
如果我有下面的表格，假设我希望将具有这些订单的项目保存到一起Item表（当然Item PK将通过OUTPUT子句生成）： - 1、3、4 和 5（9 项） - 2（9 项） - 6, 7（4 项）
订单可以按任何顺序插入，并且最好需要按创建顺序插入 Suborder 和 Items 元素。 想象一下，我将使用 While 循环和 TOP (Z) 以及适当的连接查询来选择项目（属于 Parent-s 的孙子和关联的子元素） 插入交易中。
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
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).
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, 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论