Stored proc to copy relational data (SQL Server 2000)

Question

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.

Answer 1

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

Answer 2

There are many efficient ways of doing this SQL 2005 and 2008. Here's a way to do it using 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.

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.

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 3

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

no loop necessary in 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...