SQL Loop through Inserts and Update table

Question

I have a table (tblStagingHeader) containing the below two columns and example data. It is a record of SQL table names that have been created as part of an SSIS\ETL load.

StageTable       Inserted
-------------------------
tblStage1          0
tblStage2          0
tblStage3          0

Using the below SQL task, I dynamically insert data from the staging tables (e.g. tblStage1 etc) into real tables.

My question is - after each successful insert, how do I loop through the tblStagingHeader and change the 'Inserted' field from a '0' to a '1' for each corresponding StageTable?

--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 *,
'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]  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

I cannot take all credit - this is also linked to post: SQL Insert via mappings table

Answer 1

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