Duplicate rows in tables linked by FKs

user_1484041 0
Viewed 51

I'm trying to generate some data for testing by duplicating existing data in my database. There are a number of tables linked by FKs and I want to keep the same data profile. I think it's easiest to explain with an example:

CustomerID Name Age
1 Fred 20
2 Bob 30
3 Joe 40
InvoiceID CustomerID Date
1 1 2020-01-01
2 2 2020-02-02
3 2 2020-03-03
4 3 2020-04-04
LineItemID InvoiceID Item Price Qty
1 1 Apples 1.5 5
2 2 Oranges 2 3
3 2 Peaches 2.5 6
4 3 Grapes 3 10
5 4 Pineapple 5 1

I want to duplicate all the customers who are older than 18, including all of their linked data. So for the Customers table it's easy enough to do something like:

INSERT INTO Customers (Name, Age)
SELECT Name, Age FROM Customers WHERE Age > 18

However if I did this for the other 2 tables it would result in the original Customers having double the Invoices and the new Customers having none (and similarly the original Invoices having double the LineItems and the new Invoices having none). See below for what I want the data to look like:

CustomerID Name Age
1 Fred 20
2 Bob 30
3 Joe 40
4 Bob 30
5 Joe 40
InvoiceID CustomerID Date
1 1 2020-01-01
2 2 2020-02-02
3 2 2020-03-03
4 3 2020-04-04
5 4 2020-02-02
6 4 2020-03-03
7 5 2020-04-04
LineItemID InvoiceID Item Price Qty
1 1 Apples 1.5 5
2 2 Oranges 2 3
3 2 Peaches 2.5 6
4 3 Grapes 3 10
5 4 Pineapple 5 1
6 5 Oranges 2 3
7 5 Peaches 2.5 6
8 6 Grapes 3 10
9 7 Pineapple 5 1

The only solution I've found so far is looping over the data I want to duplicate with a Cursor and inserting it one row/table at a time. Is there a better (and more importantly, faster) way to do this?

tsql sql-server sql database
2 Answers

You can use MERGE along with the OUTPUT clause to capture the mapping of new to old data, e.g.

DECLARE @CustomerMapping TABLE (OldCustomerID INT NOT NULL, NewCustomerID INT NOT NULL);

MERGE Customers AS t
USING 
(   SELECT  CustomerID, Name, Age 
    FROM    Customers 
    WHERE   Age > 18
) AS s
    ON 1 = 0 -- THIS WILL NEVER BE TRUE SO WILL ALWAYS BE "NOT MATCHED"
WHEN NOT MATCHED THEN 
    INSERT (Name, Age)
    VALUES (s.Name, s.Age)
OUTPUT s.CustomerID, inserted.CustomerID
    INTO @CustomerMapping (OldCustomerID, NewCustomerID);

You can then use this mapping of ID's to repeat the same for your invoices:

DECLARE @InvoiceMapping TABLE (OldInvoiceID INT NOT NULL, NewInvoiceID INT NOT NULL);

MERGE Invoices AS t
USING 
(   SELECT  i.InvoiceID,
            CustomerID = m.NewCustomerID,
            i.Date
    FROM    Invoices AS i
            INNER JOIN @CustomerMapping AS m
                ON m.OldCustomerID = i.CustomerID
) AS s
    ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (CustomerID, Date)
    VALUES (s.CustomerID, s.Date)
OUTPUT s.InvoiceID, inserted.InvoiceID
    INTO @InvoiceMapping (OldInvoiceID, NewInvoiceID);

You can then use your invoice mappings to insert your line items:

INSERT LineItems (InvoiceID, Item, Price, Qty)
SELECT  m.NewInvoceID,
        li.Item,
        li.Price,
        li.Qty
FROM    LineItems AS li
        INNER JOIN @InvoiceMapping AS m
            ON m.OldInvoiceID = li.InvoiceID;

A standard INSERT will do here since the output is not required (unless LineItems have further children, in which case you'll need to use MERGE again and capture the output as before.

It is necessary to do the fake MERGE that only inserts in order to capture both the new and the old IDs, with a normal INSERT you only have access to the inserted fields in the OUTPUT clause.

There are always (justifiable) concerns about using MERGE in SQL Server due to the number of bugs, however none of the bugs that I am aware of impact this scenario as long as you are using a table as the target and only inserting new data.

Example on db<>Fiddle

user_10484250

You can use the OUTPUT clause:

DECLARE @ids TABLE (customerid int, Name varchar(255), age int);

INSERT INTO Customers (Name, Age)
    OUTPUT inserted.customerid, Name, Age INTO @ids
    SELECT Name, Age
    FROM Customers
    WHERE Age > 18;

Then you can use @ids for the other tables:

INSERT INTO invoices (CustomerID, Date)
    SELECT cnew.customerID, i.date
    FROM invoices i JOIN
         customers c
         ON i.customerID = c.customerID JOIN
         @ids cnew
         ON cnew.name = c.name AND cnew.age = c.age;

The additional columns in @ids are so you can match batch to the existing column. Unfortunately, you don't have the id any more so hopefully some other combination of columns uniquely identifies the original customers.

user_11440350
Related