Copy from one table to another and replace with SCOPE_IDENTITY

Question

I'm using SQL Server 2014.

I'm denormalizing an existing database by moving all physical address columns to a dedicated Address table, like so:

Old:

Customers (CustomerId, AddressLine1, AddressLine2, City, State, Country, etc )
Employees (EmployeeId, AddressLine1, AddressLine2, City, State, Country, etc )
Orders (OrderId, AddressLine1, AddressLine2, City, State, Country, etc )

New:

Addresses (AddressId, Street, City, State, Country, Zip )
Customers (CustomerId, AddressId, etc )
Employees (CustomerId, AddressId, etc )
Orders (CustomerId, AddressId, etc )

Getting data from each table into Addresses is easy enough:

INSERT INTO Addresses (Street, City, State, Country)
    SELECT 
        CONCAT(AddressLine1, AddressLine2), City, State, Country) 
    FROM Customers
    UNION ALL
    SELECT 
        CONCAT(AddressLine1, AddressLine2), City, State, Country) 
    FROM Employees
    UNION ALL
    SELECT 
       CONCAT(AddressLine1, AddressLine2), City, State, Country) 
    FROM Orders

But how can I get the new AddressId value for each inserted row and set it as the Customers.AddressId, Employees.AddressId, and Orders.AddressId values?

...ideally without using a giant loop that gets a single row, inserts it, and updates with SCOPE_IDENTITY.

Answer 1

Assuming that

1) Adresses, Customers, Employees and Orders have the following PKs: AdressId, CustomerId, EmployeeId, OrderId

and

2) The dbo.Adresses.AdressId column (PK) has the IDENTITY property

then for every source table (Customers, etc.) you could use following approach:

/*
-- Just once
CREATE TABLE #AffectedRows (
    SourceID    INT NOT NULL PRIMARY KEY,
    AdressID    INT NOT NULL
)
*/
SET XACT_ABORT ON
BEGIN TRAN
TRUNCATE TABLE #AffectedRows

MERGE dbo.Adresses a
USING dbo.Customers c ON a.AdressID = c.AdressID
WHEN NOT MATCHED 
    THEN 
    INSERT (City)
    VALUES (c.City)
OUTPUT c.CustomerId, inserted.AdressId INTO #AffectedRows;

UPDATE  c
SET     c.AdressId = ar.AdressId
FROM    dbo.Customers c
JOIN    #AffectedRows ar ON c.CustomerId = ar.SourceId
--WHERE c.AdressId IS NULL
COMMIT

SELECT * FROM dbo.Customers