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.