动态数据映射存储过程输出重复问题求助

我有一张包含SourceTable、SourceField、DestinationTable、DestinationField、ExtraMappingLogin字段的表。为实现源表到目标表的动态数据映射，编写了如下存储过程，但执行PRINT时输出的SQL语句出现重复，我仅需插入第一行数据，不清楚重复原因，请求协助排查。

Declare   @O_Cols           sysname
        , @N_Cols           sysname
        , @O_Tabl           sysname
        , @N_Tabl           sysname
        , @InsertColsList   NVARCHAR(MAX) =''
        , @SelectColsLIst   NVARCHAR(MAX) =''
        , @Sql              NVARCHAR(MAX);

DROP TABLE IF EXISTS #Unq   
 Select distinct SourceTable,[DestinationTable], ranking 
 into #Unq from (SELECT
    SourceTable,[DestinationTable],
    DENSE_RANK () OVER ( 
        ORDER BY [DestinationTable] DESC
    ) ranking 
FROM
    ##MappingData where Destinationtable IS NOT NULL) A

DECLARE @inirow INT=1;  
DECLARE @NumberofRowint INT
DECLARE @DestTable NVARCHAR(50)  
SELECT @NumberofRowint = COUNT(*) FROM #Unq

WHILE @inirow <= @NumberofRowInt  
BEGIN

SELECT @DestTable = DestinationTable
      ,@O_Tabl = SourceTable
FROM #Unq WHERE ranking=@inirow  

DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
SELECT  SourceField AS OriginalColumn ,  DestinationField AS NewColumn

FROM ##MappingData
WHERE DestinationTable = @DestTable
OPEN Cur
  FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols

WHILE (@@FETCH_STATUS = 0)
BEGIN
        SET @SelectColsLIst += ISNULL(',' + QUOTENAME(@O_Cols), '');
        SET @InsertColsList += ISNULL(',' + QUOTENAME(@N_Cols), '');
    
    FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols
END

CLOSE Cur
DEALLOCATE Cur;

    SET @SelectColsLIst = STUFF(@SelectColsLIst, 1 ,1,'');
    SET @InsertColsList = STUFF(@InsertColsList, 1 ,1,'');

 SET @Sql = N' INSERT INTO ' + @DestTable + N'( ' + @InsertColsList + N' )'
          + N' SELECT ' + @SelectColsLIst
          + N' FROM ' + @O_Tabl;
PRINT @Sql
END
--EXEC sp_executesql @Sql;

核心原因 #

1. 变量未重置：@SelectColsLIst和@InsertColsList在循环外初始化，每次处理新目标表时未清空，导致字段拼接结果累积，最终SQL出现重复字段。
2. 循环逻辑冗余：外层WHILE会遍历#Unq所有行生成多条插入语句，不符合仅处理第一行的需求。

修正后的代码 #

Declare   @O_Cols           sysname
        , @N_Cols           sysname
        , @O_Tabl           sysname
        , @N_Tabl           sysname
        , @InsertColsList   NVARCHAR(MAX) =''
        , @SelectColsLIst   NVARCHAR(MAX) =''
        , @Sql              NVARCHAR(MAX);

DROP TABLE IF EXISTS #Unq   
Select distinct SourceTable,[DestinationTable], ranking 
into #Unq from (SELECT
    SourceTable,[DestinationTable],
    DENSE_RANK () OVER ( 
        ORDER BY [DestinationTable] DESC
    ) ranking 
FROM
    ##MappingData where Destinationtable IS NOT NULL) A

-- 直接取第一行映射关系
SELECT @DestTable = DestinationTable
      ,@O_Tabl = SourceTable
FROM #Unq WHERE ranking=1  

DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR
SELECT  SourceField AS OriginalColumn ,  DestinationField AS NewColumn
FROM ##MappingData
WHERE DestinationTable = @DestTable

OPEN Cur
FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols

WHILE (@@FETCH_STATUS = 0)
BEGIN
        SET @SelectColsLIst += ISNULL(',' + QUOTENAME(@O_Cols), '');
        SET @InsertColsList += ISNULL(',' + QUOTENAME(@N_Cols), '');
    
    FETCH NEXT FROM Cur INTO @O_Cols , @N_Cols
END

CLOSE Cur
DEALLOCATE Cur;

SET @SelectColsLIst = STUFF(@SelectColsLIst, 1 ,1,'');
SET @InsertColsList = STUFF(@InsertColsList, 1 ,1,'');

SET @Sql = N' INSERT INTO ' + @DestTable + N'( ' + @InsertColsList + N' )'
          + N' SELECT ' + @SelectColsLIst
          + N' FROM ' + @O_Tabl;
PRINT @Sql
--EXEC sp_executesql @Sql;

关键修改点 #

• 移除外层WHILE循环，直接通过ranking=1获取第一行需处理的映射关系。
• 确保字段拼接仅针对单个目标表，无累积重复。

若后续需处理多个目标表，只需在每次循环开始时重置拼接变量：

WHILE @inirow <= @NumberofRowInt  
BEGIN
    -- 重置字段拼接变量
    SET @SelectColsLIst = '';
    SET @InsertColsList = '';

    SELECT @DestTable = DestinationTable
          ,@O_Tabl = SourceTable
    FROM #Unq WHERE ranking=@inirow  

    -- 后续游标逻辑不变...
END

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