当来自三个源列时，从目标列返回输出

Question

我有一个源表和一个目标表，我的源表有三列构成我的目标目标列。我的目标列是电话，我的源列是工作号码、手机号码、家庭号码。当我查找客户帐户时，我希望在目标列中看到客户编号。因此，基本上我的三个源列中的所有内容都应位于我的一个目标列中，并具有各自的客户帐户。有没有办法通过 t-sql 或 ssis 将目标列设置为等于我的三个源列？

Answer 1

DECLARE @CustomerId INT 
DECLARE cur_insert_phone CURSOR FOR 
SELECT CustomerId FROM SourceDatabase.Schema.SourceTable 
ORDER BY CustomerId
OPEN cur_insert_phone
FETCH NEXT FROM cur_insert_phone INTO @CustomerId
WHILE @@FETCH_STATUS = 0 
BEGIN
INSERT INTO DestinationDatabase.Schema.SourceTable (CustomerId, CustomerNumber, PhoneType)
SELECT t.CustomerId, t.PhoneNumber, t.PhoneType
FROM
(SELECT CustomerId, WorkNumber,CellNumber,HomeNumber FROM SourceDatabase.Schema.SourceTable WHERE CustomerId = @CustomerId) p
UNPIVOT 
(
PhoneNumber FOR PhoneType IN (WorkNumber,CellNumber,HomeNumber)
) AS t 
FETCH NEXT FROM cur_insert_phone INTO @CustomerId
END
CLOSE cur_insert_phone
DEALLOCATE cur_insert_phone
GO