1

I need a SQL query to duplicate/clone a row and all of its child from other tables. Assume that I have 3 tables like this.

CREATE TABLE [dbo].[Order]
(
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](20) NULL
)

CREATE TABLE [dbo].[OrderDetail]
(
    [OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
    [OrderID] [int] NOT NULL,
    [Detail] [varchar](20) NULL
)

CREATE TABLE [dbo].[CustomProduct]
(
    [CustomProductID] [int] IDENTITY(1,1) NOT NULL,
    [OrderDetailID] [int] NOT NULL,
    [Custom] [varchar](20) NULL
)

INSERT INTO Order 
VALUES ('Customer1'),('Customer2')

INSERT INTO OrderDetail 
VALUES (1, 'Customer1_Detail_1'), (1, 'Customer1_Detail_2'),
       (2, 'Customer2_Detail_1'), (2, 'Customer2_Detail_2')

INSERT INTO CustomProduct 
VALUES (1, 'Customer1_Detail_1_Custom1'),
       (1, 'Customer1_Detail_1_Custom2'),
       (2, 'Customer1_Detail_2_Custom1'),
       (2, 'Customer1_Detail_2_Custom2'),
       (3, 'Customer2_Detail_1_Custom1'),
       (3, 'Customer2_Detail_1_Custom2'),
       (4, 'Customer2_Detail_2_Custom1'),
       (4, 'Customer2_Detail_2_Custom1')

Data will be like this:

Order:

1   Customer1
2   Customer2

OrderDetail:

1   1   Customer1_Detail_1
2   1   Customer1_Detail_2
3   2   Customer2_Detail_1
4   2   Customer2_Detail_2

CustomProduct:

1   1   Customer1_Detail_1_Custom1
2   1   Customer1_Detail_1_Custom2
3   2   Customer1_Detail_2_Custom1
4   2   Customer1_Detail_2_Custom2
5   3   Customer2_Detail_1_Custom1
6   3   Customer2_Detail_1_Custom2
7   4   Customer2_Detail_2_Custom1
8   4   Customer2_Detail_2_Custom1

Now I need a SQL query to clone Order1 into New Order and all of its children rows. Like this:

Order:

1   Customer1
2   Customer2
3   Customer1_cloned

OrderDetail:

1   1   Customer1_Detail_1
2   1   Customer1_Detail_2
3   2   Customer2_Detail_1
4   2   Customer2_Detail_2
5   3   Customer1_Detail_1_cloned
6   3   Customer1_Detail_2_cloned

CustomProduct:

1   1   Customer1_Detail_1_Custom1
2   1   Customer1_Detail_1_Custom2
3   2   Customer1_Detail_2_Custom1
4   2   Customer1_Detail_2_Custom2
5   3   Customer2_Detail_1_Custom1
6   3   Customer2_Detail_1_Custom2
7   4   Customer2_Detail_2_Custom1
8   4   Customer2_Detail_2_Custom1
9   5   Customer1_Detail_1_Custom1_cloned
10  5   Customer1_Detail_1_Custom2_cloned
11  6   Customer1_Detail_2_Custom1_cloned
12  6   Customer1_Detail_2_Custom2_cloned

This answer is similar but not as I Expected Base on that solution, you have to clone CustomProduct (child Table) first, and prepresent Parent's ID or identity colums to insert. In my case, since the IDs are automatically incremented and we could not find any identity to insert to parent table.

Anh Bảy
  • 739
  • 1
  • 6
  • 15
  • Before you tell us what you need, please do show us what you've tried and explain why it didn't work. Stack Overflow is *not* a free coding service. – Thom A Jan 16 '19 at 16:21
  • 1
    You started good with the DDL but the sample data should be as DML... – Zohar Peled Jan 16 '19 at 16:22
  • Your problem is that you may lose track of `OrderID` when you clone row in `OrderDetail`. I suggest 2 ways to fix this. One way is using a store procedure X to do all your clone in 3 tables. In X use a loop each row in `OrderDetail` to clone, and insert it original `OrderID`, new `OrderID` and new `OrderDetailID` to a temporary table #tmp. Then use that #tmp to join and insert clone to CustomProduct. – Pham X. Bach Jan 18 '19 at 02:36
  • Second is create a table `OrderDetail_2` with same structure with `OrderDetail` + 1 column `origin_OrderID`. Insert to `OrderDetail_2` first then insert from `OrderDetail_2` to `OrderDetail` (using index in `origin_OrderID` for fast select this step). Then use `OrderDetail_2` to insert into `CustomProduct`. Finnally drop `OrderDetail_2`. – Pham X. Bach Jan 18 '19 at 02:36
  • Edited: in all 2 ways above, you should track original `OrderDetailID` in one more column, too – Pham X. Bach Jan 18 '19 at 02:56

0 Answers0