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

Etlworks Integrator supports extracting data from the database objects, 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.

Step 6. If needed, specify the exclusion list in the Exclude Objects field under the MAPPING->Parameters.

Step 7. Configure MERGE (UPSERT) if needed.

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

Step 8. 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 9. 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 10. Save the flow and execute it manually.

In Schedules

Step 11. Schedule the flow to be executed periodically.