如何使用 TSQL 脚本将一个表拆分为另一个表

2021-09-10 00:00:00 tsql sql-server

到目前为止我有这个代码:

I have this code so far:

DECLARE @AddressIDS TABLE
(
    AddressID int
)

INSERT INTO Address(Street1, City, StateCode, ZipCode)
OUTPUT inserted.AddressID INTO @AddressIDS
SELECT Street1, City, StateCode, ZipCode
    FROM Contact

现在我需要将 @AddressIDS 中的 ID 放回到 Contact 表的 AddressID 中.

Now I need to put the IDs in the @AddressIDS back into the AddressID of the Contact table.

这更好地描述了我在 TSQL 中尝试做的事情:

This better describes what I'm trying to do in TSQL:

推荐答案

假设 Street1 + City + StateCode + ZipCode 是唯一的，或者多个联系人可以共享同一个 AddressID，那么您就不需要推测 AddressID 值在一个中间步骤.您只需执行插入地址，然后:

Assuming Street1 + City + StateCode + ZipCode is unique, or that multiple contacts can share the same AddressID, then you don't need to suss out the AddressID values in an intermediate step. You can just perform your insert into Address and then:

UPDATE c 
  SET AddressID = a.AddressID
  FROM dbo.Contact AS c
  INNER JOIN dbo.Address AS a
  ON c.Street1 = a.Street1
  AND c.City = a.City
  AND c.StateCode = a.StateCode 
  AND c.ZipCode = a.ZipCode;

相关文章