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


tblStage2

Field_PLM   Field_WER   Field_YTE
 xxxxx       xxxxxx      xxxxxxx

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

tblDest1
FldABC    FldZXC    FldQWE


tblDest2
FldPLM  FldWER   FldYTE

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 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 at 15:32
  • how do I do that? – Michael Dec 4 at 15:34
up vote 2 down vote accepted

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])
VALUES
    ('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] + '(' +
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

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;
  • This is useful - I will come back to you....home time here... – Michael Dec 4 at 17:06
  • @Michael - Understood - let me know if it works for you :) – Scott Hodgin Dec 4 at 17:07
  • It is working! I have a couple of issues with data conversion issues as the staging tables are all nvarchar100 and the real tables are not – Michael Dec 5 at 9:34
  • 1
    @Michael - Glad it's working for you. As far as the mismatch in column types, perhaps you could create views of the staging and real tables that CAST/CONVERT to the correct data types and use the views instead of the real tables? – Scott Hodgin Dec 5 at 9:48
  • Yes - that is a good idea - many thanks for your help! – Michael Dec 5 at 9:55

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.