SQL Server客户数据迁移：扩展OUTPUT临时表列数报错解决

问题描述 #

正在执行客户记录从数据源到SQL Server生产客户数据库的迁移任务，已将待迁移记录导入扁平表。需要将数据插入生产库后，用生成的CustomerID插入多个关联链接表（生产架构非本人搭建）。目前已实现捕获生成的CustomerID并插入其中一个链接表，但尝试将临时表#RowInserted扩展至3列（新增@CustomerType变量并修改FETCH等操作）时，出现**“列未识别”**报错。需求是将#RowInserted扩展至最多5列。

现有SQL代码：

USE OtherCustomerDB
GO

DECLARE @CustomerRef AS INT;
DECLARE @CustomerDepot AS INT

DECLARE CustomerCursor CURSOR FOR
SELECT CustRefNum, PreferredDepot FROM CompanyCustomersDB;

OPEN CustomerCursor;

FETCH NEXT FROM CustomerCursor INTO @CustomerRef, @CustomerDepot;

WHILE @@FETCH_STATUS = 0
BEGIN

    DROP TABLE IF EXISTS #RowInserted;
    CREATE TABLE #RowInserted (ID INT, PrefDepotID INT);

    Insert INTO dbo.Customer (CompanyName, ContactForename, ContactSurname, CompanyAddress)
    OUTPUT INSERTED.Id, @CustomerDepot INTO #RowInserted(ID, PrefDepotID)
    SELECT BusinessName, ManagerFirstName, ManagerSurname, CompanyAddress FROM dbo.CompanyCustomerDB
    WHERE CustRefNum = @CustomerRef;

    SELECT * FROM #RowInserted;

    INSERT INTO dbo.[PreferredDepot] (CustomerId, PreferredDepotID)
    (SELECT ID, PrefDepotID from #RowInserted);

    FETCH NEXT FROM CustomerCursor INTO @CustomerRef, @CustomerDepot;
END;

CLOSE CustomerCursor;
DEALLOCATE CustomerCursor;
GO

解决方案 #

报错核心原因是游标变量与SELECT列不匹配、OUTPUT子句列与临时表列未对齐。以下是扩展临时表的标准步骤：

1. 扩展游标与变量 #

首先在游标中包含所有需要后续插入关联表的字段，同时声明对应的变量。例如要新增CustomerType和SalesRegion字段：

DECLARE @CustomerRef AS INT;
DECLARE @CustomerDepot AS INT;
DECLARE @CustomerType AS INT;
DECLARE @SalesRegion AS VARCHAR(50);

DECLARE CustomerCursor CURSOR FOR
SELECT CustRefNum, PreferredDepot, CustomerType, SalesRegion FROM CompanyCustomersDB;

2. 修改临时表结构 #

根据需要扩展#RowInserted的列数，确保包含INSERTED返回的字段和所有需要的变量字段：

CREATE TABLE #RowInserted (
    ID INT,
    PrefDepotID INT,
    CustType INT,
    SalesRegion VARCHAR(50)
);

3. 对齐OUTPUT子句与临时表列 #

在INSERT的OUTPUT子句中，同时输出INSERTED生成的ID和所有变量值，与临时表的列一一对应：

Insert INTO dbo.Customer (CompanyName, ContactForename, ContactSurname, CompanyAddress)
OUTPUT 
    INSERTED.Id, 
    @CustomerDepot, 
    @CustomerType, 
    @SalesRegion 
INTO #RowInserted(ID, PrefDepotID, CustType, SalesRegion)
SELECT BusinessName, ManagerFirstName, ManagerSurname, CompanyAddress FROM dbo.CompanyCustomerDB
WHERE CustRefNum = @CustomerRef;

4. 更新FETCH语句 #

确保FETCH NEXT的变量数量和顺序与游标SELECT的字段完全一致：

FETCH NEXT FROM CustomerCursor INTO @CustomerRef, @CustomerDepot, @CustomerType, @SalesRegion;

5. 插入多关联表 #

利用临时表中的多列数据，插入对应的关联表：

-- 插入PreferredDepot表
INSERT INTO dbo.[PreferredDepot] (CustomerId, PreferredDepotID)
SELECT ID, PrefDepotID from #RowInserted;

-- 插入CustomerType关联表
INSERT INTO dbo.[CustomerTypeMapping] (CustomerId, TypeId)
SELECT ID, CustType from #RowInserted;

-- 插入SalesRegion关联表
INSERT INTO dbo.[CustomerRegion] (CustomerId, RegionCode)
SELECT ID, SalesRegion from #RowInserted;

完整扩展至4列的示例代码 #

USE OtherCustomerDB
GO

DECLARE @CustomerRef AS INT;
DECLARE @CustomerDepot AS INT;
DECLARE @CustomerType AS INT;
DECLARE @SalesRegion AS VARCHAR(50);

DECLARE CustomerCursor CURSOR FOR
SELECT CustRefNum, PreferredDepot, CustomerType, SalesRegion FROM CompanyCustomersDB;

OPEN CustomerCursor;

FETCH NEXT FROM CustomerCursor INTO @CustomerRef, @CustomerDepot, @CustomerType, @SalesRegion;

WHILE @@FETCH_STATUS = 0
BEGIN

    DROP TABLE IF EXISTS #RowInserted;
    CREATE TABLE #RowInserted (
        ID INT,
        PrefDepotID INT,
        CustType INT,
        SalesRegion VARCHAR(50)
    );

    Insert INTO dbo.Customer (CompanyName, ContactForename, ContactSurname, CompanyAddress)
    OUTPUT 
        INSERTED.Id, 
        @CustomerDepot, 
        @CustomerType, 
        @SalesRegion 
    INTO #RowInserted(ID, PrefDepotID, CustType, SalesRegion)
    SELECT BusinessName, ManagerFirstName, ManagerSurname, CompanyAddress FROM dbo.CompanyCustomerDB
    WHERE CustRefNum = @CustomerRef;

    -- 插入多个关联表
    INSERT INTO dbo.[PreferredDepot] (CustomerId, PreferredDepotID)
    SELECT ID, PrefDepotID from #RowInserted;

    INSERT INTO dbo.[CustomerTypeMapping] (CustomerId, TypeId)
    SELECT ID, CustType from #RowInserted;

    INSERT INTO dbo.[CustomerRegion] (CustomerId, RegionCode)
    SELECT ID, SalesRegion from #RowInserted;

    FETCH NEXT FROM CustomerCursor INTO @CustomerRef, @CustomerDepot, @CustomerType, @SalesRegion;
END;

CLOSE CustomerCursor;
DEALLOCATE CustomerCursor;
GO

扩展至5列的注意事项 #

如果需要扩展到5列，只需重复上述步骤：

• 在游标SELECT中添加第5个字段
• 新增对应的变量
• 在#RowInserted中添加第5列
• 在OUTPUT子句中加入该变量，对应临时表列
• 更新FETCH语句的变量列表

关键要保证游标SELECT字段数=变量数=FETCH变量数=临时表列数=OUTPUT输出列数，所有环节一一对应即可避免“列未识别”报错。

内容的提问来源于stack exchange，提问作者Jafar95