SQL Loop through Inserts and Update table
You should be able to adjust the dynamic SQL to include the UPDATE for each staging table.  

Also, based on your comment about the update only being executed when the insert is successful, I modified the code example to wrap each insert/update into a transaction which should be an all-or-nothing for that staging table.

UPDATE: Added TRY/CATCH logic

--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'BEGIN TRY BEGIN TRANSACTION;  insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' + 
' select ' + 
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble] + '; update StagingHeader set inserted = 1 where StageTable = ''' + [StageTble] + '' + '; COMMIT; END TRY BEGIN CATCH ROLLBACK; END 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




BEGIN TRY BEGIN TRANSACTION;  insert into tblDest1(FldABC,FldZXC,FldQWE) select Field_ABC,Field_ZXC,Field_QWE from tblStage1; update StagingHeader set inserted = 1 where StageTable = 'tblStage1; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;';
BEGIN TRY BEGIN TRANSACTION;  insert into tblDest2(FldPLM,FldWER,FldYTE) select Field_PLM,Field_WER,Field_YTE from tblStage2; update StagingHeader set inserted = 1 where StageTable = 'tblStage2; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;';