How to move tables matching a wildcard name from one schema or database to another

Etlworks Integrator supports extracting data from the database objects such as tables and views, matching the wildcard name and loading it into the different database or schema.

In Connections

Step 1. Create a source connection for the relational database. Test it.

Step 2. Create a destination connection for the relational database. Test it.

 

In Flows

Step 3. Create a new database to database flow.

Step 4. Add a new transformation and select from and to connections.

Step 5. Specify the fully qualified wildcard source name in the from. For example public.*. Configure the destination name in the to using either only asterisk character (*) or any part of the destination name with the asterisk character (public.*). The system will substitute the asterisk character with the actual source object name (excluding database and schema names). For example, if the actual source name is test.dbo.patient and the destination is public.* the system will load data into the public.patient.

By default, all tables and all views that match the wildcard name in FROM will be included. If you want to exclude specific database objects enter a comma-separated list of objects to exclude in the Exclude objects field. If you want to include only specific database objects enter a comma-separated list of the objects in the Include objects field. 

Read how to programmatically change the destination (TO) name.

Step 6. Optionally, specify the exclusion list in the Exclude Objects field under the MAPPING->Parameters. Use fully-qualified tables names. The wildcard names are allowed. 

To exclude all views enter all views in the Exclude objects field.

To exclude all tables enter all tables in the Exclude objects field.

Step 7. Optionally specify the inclusion list in the Include Objects field under the MAPPING->Parameters. Use fully-qualified tables names. The wildcard names are allowed.

 

Step 8. Optionally configure the Source query. You can use token {table} in the source query.

Step 9. Configure MERGE (UPSERT) if needed.

Do not enter the Lookup Fields. Instead, enable the Predict Lookup Fields checkbox.

Alternatively to enabling the Predict Lookup Fields option (which is not always accurate) you can specify the list of table=fields pairs in the Lookup Field. Use the fully-qualified table names and ';' as a separator between table=field pairs.

Example:

test1.inventory=inventory_id,database_name;
test1.payment=payment_id,database_name;
test1.rental=rental_id,database_name;

 

Step 10. Configure change replication if needed.

Use token {TABLE} in the High Watermark Field Value if you are calculating HWM. The system will substitute a token with the actual destination table name.

Step 11. If you want to do a full refresh of the destination tables you can enter the following SQL in the Before SQL field:

truncate table {TABLE}

Step 12. Enable parallel processing if you want this flow to work faster. Remember that parallel processing is not always going to be an option, specifically when the destination database objects already exist and they do have foreign constraints between each other. Use the following technique to limit the number of parallel threads.   

Step 13. Save the flow and execute it manually.

In Schedules

Step 14. Schedule the flow to be executed periodically.