Stored proc for dynamic data mapping

30 views Asked by At

I have a table with folowing fields SourceTable,SourceField,DestinationTable,DestinationField,ExtraMappingLogin

when i am printing its geting repeated. I just need the first row to insert. I am really not sure why i am getting this.

I have tried to check. Can someone help me on gthis. this is to just map the data from source to destination.

  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;
0

There are 0 answers