As part of a SSIS ETL process, I have multiple staging tables. I have used xxxx just to resemble data: e.g. tblStage1

Field_ABC  Field_ZXC  Field_QWE
 xxxx       xxxxxxxxx   xxxxx


Field_PLM   Field_WER   Field_YTE
 xxxxx       xxxxxx      xxxxxxx

I then have the 'real' tables that they need to be inserted into:

FldABC    FldZXC    FldQWE


Using info from the Information Schema I have built a mapping table that details which table and field from the staging tables map to which real table: tblMapping:

StageTble   StageColumn   RealTable    RealCol    OrdinalPosition
tblStage1   Field_ABC     tblDest1     FldABC     1
tblStage1   Field_ZXC     tblDest1     FldZXC     2
tblStage1   Field_QWE     tblDest1     FldQWE     3
tblStage2   Field_PLM     tblDest2     FldPLM     1
tblStage2   Field_WER     tblDest2     FldWER     2
tblStage2   Field_YTE     tblDest2     FldYTE     3

How do I loop through each staging table and insert into the real tables\columns based on the values in tblMapping?

Would this be possible via a Cursor and a Loop?

Any help would be greatly appreciated.

  • If you're using SSIS you can load that table into an object variable and pass that to a for each loop container. – Zane Dec 4 '18 at 15:31
  • Use the mapping table to generate the stored procedures that you will use to load from the staging tables to the real tables. – David Browne - Microsoft Dec 4 '18 at 15:32
  • how do I do that? – Michael Dec 4 '18 at 15:34

Perhaps you could create an SSIS script process to dynamically build the insert commands and execute them.

--Demo setup
drop table if exists MappingTable;
create table MappingTable 
    ([StageTble] varchar(9), [StageColumn] varchar(9), [RealTable] varchar(8), [RealCol] varchar(6), [OrdinalPosition] int)

INSERT INTO MappingTable
    ([StageTble], [StageColumn], [RealTable], [RealCol], [OrdinalPosition])
    ('tblStage1', 'Field_ABC', 'tblDest1', 'FldABC', 1),
    ('tblStage1', 'Field_ZXC', 'tblDest1', 'FldZXC', 2),
    ('tblStage1', 'Field_QWE', 'tblDest1', 'FldQWE', 3),
    ('tblStage2', 'Field_PLM', 'tblDest2', 'FldPLM', 1),
    ('tblStage2', 'Field_WER', 'tblDest2', 'FldWER', 2),
    ('tblStage2', 'Field_YTE', 'tblDest2', 'FldYTE', 3)

SSIS script task

--SSIS script task
--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] + '(' +
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' + 
' select ' + 
        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 = 
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd
--EXECUTE sp_executesql @cmd

Produces the following dynamic SQL

insert into tblDest1(FldABC,FldZXC,FldQWE) select Field_ABC,Field_ZXC,Field_QWE from tblStage1;
insert into tblDest2(FldPLM,FldWER,FldYTE) select Field_PLM,Field_WER,Field_YTE from tblStage2;

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.