Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [DestTable], [SourceFile] from [tbl_IN_Ctrl_Dtl]
),
InsertCommands as
(
-- columns from Destination table
select *,
'BEGIN TRY insert into ' + [DestTable] + '(' +
STUFF((
        SELECT ',' + [DestCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY DestCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
-- columns from source table
STUFF((
        SELECT ',' + [SourceCol]
        FROM [tbl_IN_Ctrl_Dtl] t1
        where t1.DestTable = drt.DestTable
        GROUP BY SourceCol,ORDINAL_POSITION
        ORDER BY ORDINAL_POSITION
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
        + ' from ' + [SourceFile]
        + ';
        update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''
        +';
        update tbl_IN_Ctrl_Header set SourceFile_INSERT = 1 where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''
        +'; END TRY BEGIN CATCH'
        as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

--print @cmd

EXECUTE sp_executesql @cmd

update tbl_IN_Ctrl_Header set ErrMsg = @@ERROR where SourceFile_INSERT = 0 and SourceFile = ''' + [SourceFile]+ ''''