What is best wayto copy objects hierarchy in SQL server

Question

Assume we have hierarchy which consists from several objects, like this:

enter image description here

And I want to copy this tree (and maybe somehow change those objects).

A simple method to do this is just iterate between objects and create it one-by-one. But performance here is very poor. In addition, i do not like loops ;-)

So question - is it possible to do with set-based logic?

Have a look at [this question](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id). The technique discussed there should allow you to achieve what you want the way you want. — Andriy M, Jun 24 '11 at 16:27

score 3 · Accepted Answer · edited May 23 '17 at 12:19

Using the technique described in this question, you could do something like this:

DECLARE @CartId int, @NewCartId int;
DECLARE @CartItems TABLE (OldId int, NewId int);

SET @CartId = ...;

INSERT INTO Shopping_Cart (
  Session_Id_Str,
  Name,
  Program_User_Id,
  Shopping_Cart_Status_Code,
  Placed_Order_Id)
SELECT
  Shopping_Cart_Id,
  Session_Id_Str,
  Name,
  Program_User_Id,
  Shopping_Cart_Status_Code,
  Placed_Order_Id
FROM Shopping_Cart
WHERE Shopping_Cart_Id = @CartId;
SET @NewCartId = SCOPE_IDENTITY();

WITH src AS (
  SELECT
    Shopping_Cart_Item_Id,
    @NewCartId AS Shopping_Cart_Id,
    Base_Product_Id,
    Product_Category_Id,
    Shop_Id,
    Currency_Code
  FROM Shopping_Cart_Item
  WHERE Shopping_Cart_Id = @CartId
)
MERGE Shopping_Cart_Item AS tgt
USING src
ON 0 = 1
WHEN NOT MATCHED THEN
  INSERT (
    Shopping_Cart_Id,
    Base_Product_Id,
    Product_Category_Id,
    Shop_Id,
    Currency_Code)
  VALUES (
    src.Shopping_Cart_Id,
    src.Base_Product_Id,
    src.Product_Category_Id,
    src.Shop_Id,
    src.Currency_Code)
OUTPUT
  src.Shopping_Cart_Item_Id,
  inserted.Shopping_Cart_Item_Id
INTO @CartItems (OldId, NewId);

WITH src AS (
  SELECT
    s.Shopping_Cart_Item_Detail_Id,
    map.NewId AS Shopping_Cart_Item_Id,
    s.Display_Order_Number,
    s.Product_Id,
    s.Product_Variant_Id
  FROM Shopping_Cart_Item_Detail s
    INNER JOIN @CartItems map ON s.Shopping_Cart_Item_Id = map.OldId
)
MERGE Shopping_Cart_Item_Detail AS tgt
USING src
ON 0 = 1
WHEN NOT MATCHED THEN
  INSERT (
    Shopping_Cart_Item_Id,
    Display_Order_Number,
    Product_Id,
    Product_Variant_Id)
  VALUES (
    src.Shopping_Cart_Item_Id,
    src.Display_Order_Number,
    src.Product_Id,
    src.Product_Variant_Id);

It is assumed that you only want to copy one 'tree' at a time.

All this should probably be done in a single transaction.

What is best wayto copy objects hierarchy in SQL server

1 Answers1