sql-server - 从一个表复制到另一个表并替换为 SCOPE_IDENTITY

Question

我使用的是 SQL Server 2014。

我通过将所有物理地址列移动到专用地址表来对现有数据库进行非规范化，如下所示:

旧:

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

新:

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

从每个表中获取数据到 Addresses 非常简单:

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

但是我怎样才能为每个插入的行获取新的 AddressId 值并将其设置为 Customers.AddressId, Employees.AddressId,和 Orders.AddressId 值？

...理想情况下，无需使用获取单行、插入行并使用 SCOPE_IDENTITY 进行更新的巨大循环。

Answer 1

假设

1) Adresses、Customers、Employees 和 Orders 具有以下 PK:AdressId、CustomerId、EmployeeId、OrderId

和

2) dbo.Adresses.AdressId 列 (PK) 具有 IDENTITY 属性

然后对于每个源表(客户等)，您可以使用以下方法:

/*
-- 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