This tutorial walks through extracting data from one relational database and loading it into another database — or a different schema in the same database.
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 to the source connection and table; set TO to the destination connection and table.
- Optional: click Configure and enter a custom Source SQL. Leave it blank to extract everything from the source table.
- Optional: configure per-field mapping when the source and destination schemas differ.
- Test the transformation.
- Optional: enable MERGE (UPSERT) to update existing records instead of just inserting.
Note: Enable Predict Lookup Fields to let the flow pick the merge keys automatically, or set them manually. The manual form takes fully qualified table names with semicolon-separated table=fields pairs in Lookup Fields:
- Optional: configure change replication using a high watermark to load only the rows that changed since the last run.
- Optional: to fully refresh destination tables on every run, add this to Before SQL:
truncate table {TABLE} - Add as many source-to-destination transformations as you need.
- Save the flow and execute it manually.
- Schedule the flow: go to Schedules and create a schedule to run it periodically.
Next steps
To move multiple tables in one transformation using a wildcard, see How to ETL data from tables matching a wildcard.