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

Employee

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

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.
 
image1.png
 
The Specify Table List which is a 'tFixedFlowInput' component has a static list of table:
 
image2.png

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.

 

image3.png

 

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.

 

image4.png

 

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


Accepted Solutions
Sixteen Stars

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


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. 


All Replies
Forteen Stars TRF
Forteen Stars

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

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.

 


TRF
Employee

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

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

Forteen Stars TRF
Forteen Stars

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

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

TRF
Sixteen Stars

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


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. 

Employee

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

Thanks Rob. That worked... Man Happy