This tutorial walks through extracting data from many tables (or views) in the source database using a wildcard pattern, and loading it into the matching destination tables. Etlworks maps each source object to its destination automatically and can include, exclude, transform, and run them in parallel.
How do I set this up?
- In Connections, create a connection for the source database and test it.
- Create a connection for the destination database and test it.
- In Flows, create a new Database to database flow.
- Add a transformation. Set FROM and TO to the source and destination connections.
- In FROM, enter the fully qualified source name with a wildcard, for example public.*. In TO, use the asterisk character to map source names to destination names dynamically. For example, public.* → public.patient when the source is test.dbo.patient.
- Optional: on Configuration > Parameters, configure how to include or exclude tables:
- Exclude objects — skip specific tables / views. Comma-separated list of fully qualified names; wildcards are allowed. Example: *.v_* excludes all views named with a v_ prefix.
- Include objects — load only these tables / views. Fully qualified names, with or without wildcards.
Enter all views in Exclude objects to exclude every view. Enter all tables to exclude every table.
- Optional: in Source Query, use the {table} token to reference the dynamic source table name.
- Optional: enable MERGE (UPSERT) to update existing records.
Note: Enable Predict Lookup Fields to let the flow pick keys automatically, or set them manually as fully qualified table=fields pairs in Lookup Fields:
- Optional: configure change replication using a high watermark to load only changed rows.
- Optional: to fully refresh destination tables on every run, add this to Before SQL:
truncate table {TABLE} - Optional: enable Use Parallel Threads when processing sources by a wildcard for better performance. Be careful when destination tables have foreign-key constraints — parents must load before children. The default thread count is 5; you can change the number of threads.
- Save the flow and execute it manually.
- Schedule the flow: go to Schedules and create a schedule to run it periodically.