存储proc以复制关系数据(SQL Server 2000)

I've got the following tables (only key columns shown):

我有以下表格(只显示关键栏):

Order     OrderItem     OrderItemDoc   Document
=======   ===========   ============   ==========
OrderId   OrderItemId   OrderItemId    DocumentId
--etc--   OrderId       DocumentId     --etc--
          --etc--

I'm writing a stored procedure to 'clone' an Order (takes an existing OrderId as a parameter, copies the Order and all related items, then returns the new OrderId). I'm stuck on the 'OrderItemDoc' joining table as it will be joining two sets of newly created records. I'm thinking I'll need to loop round a temporary table that maps the old IDs to the new ones. Is that the right direction to go in? It's running on MS-SQL 2000.

我正在编写一个存储过程来“克隆”一个订单(使用一个现有的OrderId作为参数，复制订单和所有相关项，然后返回新的OrderId)。我被困在“OrderItemDoc”连接表上，因为它将连接两组新创建的记录。我想我需要对一个临时表进行循环，该表将旧id映射到新id。这是正确的方向吗?它运行在MS-SQL 2000上。

3 个解决方案

#1

There are many efficient ways of doing this SQL 2005 and 2008. Here's a way to do it using SQL2000.

有许多有效的方法可以实现这一SQL 2005和2008。这里有一种使用SQL2000的方法。

You need to declare a variable to hold the cloned OrderId and create a temp table to hold the cloned records that will go in the OrderItemDoc table.

您需要声明一个变量来保存已克隆的OrderId，并创建一个临时表来保存将放入OrderItemDoc表的克隆记录。

Here's some sample code on how to that. It relies on the sequence to link the old OrderItems to the new ones in OrderItemDoc Table.

这里有一些示例代码。它依赖于序列将旧的OrderItems链接到OrderItemDoc表中的新订单。

CREATE PROCEDURE CloneOrder
(
  @OrderId int
)
AS
DECLARE @NewOrderId int

--create the cloned order
INSERT Order(...OrderColumnList...)
SELECT ...OrderColumnList... FROM ORDER WHERE OrderId = @OrderId;

-- Get the new OrderId
SET @NewOrderId = SCOPE_IDENTITY();

-- create the cloned OrderItems
INSERT OrderItem(OrderId,...OrderItemColumns...)
SELECT @NewOrderId, ...OrderItemColumns... 
FROM OrderItem WHERE OrderId = @OrderId

-- Now for the tricky part
-- Create a temp table to hold the OrderItemIds and DocumentIds
CREATE TABLE #TempOrderItemDocs
(
   OrderItemId int,
   DocumentId int   
)

-- Insert the DocumentIds associated with the original Order
INSERT #OrderItemDocs(DocumentId)
SELECT
    od.DocumentId
FROM
    OrderItemDoc od
    JOIN OrderItem oi ON oi.OrderItemId = od.OrderItemId
WHERE
    oi.OrderId = @OrderId
ORDER BY 
    oi.OrderItemId

-- Update the temp table to contain the newly cloned OrderItems
UPDATE #OrderItemDocs
SET 
   OrderItemId = oi.OrderItemId
FROM 
   OrderItem oi
WHERE 
   oi.OrderId = @NewOrderId
ORDER BY 
   oi.OrderItemId

-- Now to complete the Cloning process
INSERT OrderItemDoc(OrderItemId, DocumentId)
SELECT 
      OrderItemId, DocumentId
FROM 
      #TempOrderItemDocs

#2

Yes, a memory table or a temp table would be your best options. If your PK's are identity columns then you could also make assumptions about ID's being contiguous based on an offset (ie, you could assume that your new OrderItemId is equal to the existing Max(OrderItemId) in the table + the relative offset of the Item in the Order, but I don't like making assumptions like that and it becomes a pain going more than one level deep).

是的，内存表或临时表是最好的选择。如果你PK的身份列然后你也可以假设ID是连续的基于一个偏移量(例如,你可以假设你的新OrderItemId等于现有的马克斯(OrderItemId)的相对偏移表+项目的顺序,但我不喜欢做这样的假设,它变成了疼痛超过一层深)。

#3

drats, I wrote up this then saw you were on 2000... (sql server 2005 doesn't have the trick that this uses...)

德拉茨，我写了这个然后看到你在2000年……(sql server 2005并没有使用…)

no loop necessary in sql 2005..

sql 2005中不需要循环。

INSERT INTO Order        ----assuming OrderID is an identity
        VALUES ( .....)
    SELECT
        .....
    FROM Order
    WHERE OrderId=@OrderId

DECLARE @y TABLE (RowID int identity(1,1) primary key not null, OldID int, NewID int)

INSERT INTO OrderItem               ---assuming OrderItemId is an identity
        VALUES (OrderId ......)
    OUTPUT OrderItems.OrderItemId, INSERTED.tableID
    INTO @y
    SELECT
        OrderId .....
    FROM OrderItems
        WHERE OrderId=@OrderId

INSERT INTO OrderItemDoc
        VALUES (OrderItemId  ....)  ---assuming DocumentId is an identity
    SELECT
        y.NewID .....
        FROM OrderItem
            INNER JOIN @Y  y ON OrderItem.OrderItemId=y.OldId

do document the same way, make a new @temp table, etc...

以同样的方式进行文档记录，创建一个新的@temp表，等等……

#1