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

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;';
  • Thanks again for your help - one thing - if one of the insert fails, the update is still performed. For example, if the INSERT INTO tblDest2 fails, the inserted flag is still updated to 1 in the StagingHeader table? – Michael Dec 5 at 14:24
  • @Michael - you should be able to wrap each of the combined insert/update statements in a transaction - let me update the example. – Scott Hodgin Dec 5 at 14:26
  • ok thanks. It should only update the Inserted column from 0 to 1 if the INSERT is successful. – Michael Dec 5 at 14:30
  • @Michael - I updated my example - see if that works for you. – Scott Hodgin Dec 5 at 14:36
  • ah now it won't update any of the Inserted flags from 0 to 1 - they all remain at 0 – Michael Dec 5 at 14:57

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.