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;