Looping through a list of Table Names and selecting from each table in the list

[Anonymous]

Dear All

I am trying to create a Job that when executed, dynamically select from a static list of table names, iterate through the list and for each table name, it will perform a 'SELECT * FROM <Table Name> and then insert the select rows into another table of the same name in another database.

The Specify Table List which is a 'tFixedFlowInput' component has a static list of table:

The schema (row1) that is passed between tFixedFlowInput and tFlowToIterate components is row1.tableName, which carries the name of the table in the list.

The next component in the flow will iterate through the list and pass the table name to the next component which is a tDB2Input and this will perform a 'SELECT * FROM...' the table name that comes from the iterator.

The last component in the flow is the insertion into the target Database which is a tMSSqlOutput component and this will create the selected rows.

So I have the following questions:

1. How do I define a dynamic schema in the 'tDB2Input' component. Obvioulsy I do not know or can specify the schema at design time. I want the component to derive the schema name based on the table that it selects from ?

2. Can I specify 'row1.tableName' in the tMSSqlOutput component ?

Appreciate your help

Thanks

Patrice

[TRF]

Hi,

1- No dynamic schema with open source version.

2- As you have a tFlowToIterate, it's preferable to use the global variable created by this component.

You can retrieve the tablename using:

(String)globalMap.get("row1.tableName") // or an other name depending on the settings for tFlowToIterate

Hope this helps.

[Anonymous]

Thanks for the reply.

I am not using the Open Source Studio. I am using a Licenced version: 6.1.1.

Let me know

Thanks

[TRF]

No access to the licensed version at this time. Unable to complete my answer.

[Anonymous · Accepted solution]

---

1. How do I define a dynamic schema in the 'tDB2Input' component. Obvioulsy I do not know or can specify the schema at design time. I want the component to derive the schema name based on the table that it selects from ?

2. Can I specify 'row1.tableName' in the tMSSqlOutput component ?

---

1) Just select a Built-In schema in the tDB2Input component and then create a single column with a type of "Dynamic" for that. Then for the tMSSqlOutput component, do the same.

2) I don't believe you can specify row1.tableName in the tMSSqlOutput component's table name field. BUT you CAN use ((String)globalMap.get("row1.tableName")). This globalMap is set during the iteration, which is before the subjob containing the tMSSqlOutput component. Parameters like filenames and table names need to be in place before the component is first initialised in many cases.

You might also want to consider whether the tables already exist in your output database. If they don't then you will have to use "Create table if does not exist" in your output component's "Action on table" option.

Hope this helps. 

[Anonymous]

Thanks Rob. That worked...

[Anonymous]

I have to perform a similar task. Where I need to loop through a list of tables in the same DB in SQL Server and output them to a MYSQL DB.

I was wondering why a "contexts." were added to the DB Information in the Input. Do I need to make a Contexts for this Sort of Job? Also what value does the Schema need in the input to perform this type of iteration,you mentioned "Dynamic" not sure if that is a datatype or an actual value.

[Anonymous]

I have to perform a similar task. Where I need to loop through a list of tables in the same DB in SQL Server and output them to a MYSQL DB.

I was wondering why a "contexts." were added to the DB Information in the Input. Do I need to make a Contexts for this Sort of Job? Also what value does the Schema need in the input to perform this type of iteration,you mentioned "Dynamic" not sure if that is a datatype or an actual value.

Help would be greatly appreciated,

Thanks,

Andrew