SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Customers (CustomerID INT IDENTITY, Name VARCHAR(4), Age INT);

INSERT INTO Customers(Name, Age)
VALUES
	('Fred', 20),
	('Bob', 30),
	('Joe', 40);


CREATE TABLE Invoices (InvoiceID INT IDENTITY, CustomerID INT, Date DATETIME);

INSERT INTO Invoices(CustomerID, Date)
VALUES
	(1, '2020-01-01 00:00:00'),
	(2, '2020-02-02 00:00:00'),
	(2, '2020-03-03 00:00:00'),
	(3, '2020-04-04 00:00:00');


CREATE TABLE LineItems (LineItemID INT IDENTITY, InvoiceID INT, Item VARCHAR(9), Price INT, Qty INT);

INSERT INTO LineItems(InvoiceID, Item, Price, Qty)
VALUES
	(1, 'Apples', 1.5, 5),
	(2, 'Oranges', 2, 3),
	(2, 'Peaches', 2.5, 6),
	(3, 'Grapes', 3, 10),
	(4, 'Pineapple', 5, 1);
12 rows affected

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

MERGE Customers AS t
USING 
(	SELECT  CustomerID, Name, Age 
	FROM	Customers 
	WHERE	Age > 21
) 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);

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



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

SELECT * FROM Customers;


CustomerID
Name
Age


1
Fred
20

2
Bob
30

3
Joe
40

4
Bob
30

5
Joe
40


SELECT * FROM Invoices;


InvoiceID
CustomerID
Date


1
1
2020-01-01 00:00:00.000

2
2
2020-02-02 00:00:00.000

3
2
2020-03-03 00:00:00.000

4
3
2020-04-04 00:00:00.000

5
4
2020-02-02 00:00:00.000

6
4
2020-03-03 00:00:00.000

7
5
2020-04-04 00:00:00.000


SELECT * FROM LineItems;


LineItemID
InvoiceID
Item
Price
Qty


1
1
Apples
1
5

2
2
Oranges
2
3

3
2
Peaches
2
6

4
3
Grapes
3
10

5
4
Pineapple
5
1

6
5
Oranges
2
3

7
5
Peaches
2
6

8
6
Grapes
3
10

9
7
Pineapple
5
1

InvoiceID	CustomerID	Date
1	1	2020-01-01 00:00:00.000
2	2	2020-02-02 00:00:00.000
3	2	2020-03-03 00:00:00.000
4	3	2020-04-04 00:00:00.000
5	4	2020-02-02 00:00:00.000
6	4	2020-03-03 00:00:00.000
7	5	2020-04-04 00:00:00.000

LineItemID	InvoiceID	Item	Price	Qty
1	1	Apples	1	5
2	2	Oranges	2	3
3	2	Peaches	2	6
4	3	Grapes	3	10
5	4	Pineapple	5	1
6	5	Oranges	2	3
7	5	Peaches	2	6
8	6	Grapes	3	10
9	7	Pineapple	5	1