用于复制关系数据的存储过程 (SQL Server 2000)

Question

我有以下表格（仅显示关键列）：

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

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

Answer 1

在 SQL 2005 和 2008 中有许多有效的方法来执行此操作。下面是使用 SQL2000 执行此操作的方法。

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

这里有一些关于如何做到这一点的示例代码。 它依赖于将旧 OrderItems 链接到 OrderItemDoc 表中的新 OrderItems 的顺序。

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

Answer 2

是的，内存表或临时表将是您的最佳选择。 如果您的 PK 是标识列，那么您还可以根据偏移量假设 ID 是连续的（即，您可以假设您的新 OrderItemId 等于表中现有的 Max(OrderItemId) + 中 Item 的相对偏移量）秩序，但我不喜欢做出这样的假设，这会成为一种更深层次的痛苦）。

Answer 3

drats，我写了这个然后看到你在2000...（sql server 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

请记录一下同样的方式，创建一个新的@temp表，等等......