Copy from one table to another and replace with SCOPE_IDENTITY

Viewed 92

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.

2 Answers
Related