代码之家 › 专栏 › 技术社区 › Nick

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

sql-server-2000 tsql sql-server

Nick · 技术社区 · 17 年前

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

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

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

3 回复 | 直到 17 年前

Jose Basilio 17 年前

有很多有效的方法可以实现这个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

Adam Robinson 17 年前

是的,内存表或临时表是您最好的选择。如果您的pk是标识列,那么您也可以基于偏移量来假设id是连续的(即,您可以假设新的order item id等于表中现有的max(order item id)+顺序中项的相对偏移量,但我不喜欢这样做假设,而且这样做会让您更加痛苦。汉一级深)。

KM. 17 年前

德雷斯 ,我写了这个然后看到你在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表等…