About full refresh
Sometimes the simplest approach is the best one. If the source datasets are relatively small (100K or one hundred thousand records), it makes sense to consider a full refresh, an approach when the system polls all the records from the source and refreshes the destination. The records in the destination can be deleted or truncated prior to reloading, or the full refresh can be combined with the
MERGE (UPSERT). The full refresh also makes sense when other options are not available.
Read about other change replication techniques available in the Etlworks Integrator.
Create a full refresh Flow
This example is for replicating data from one database to another, but the technique works exactly the same if the source, the target, or both is not a relational database.
Step 1. Create a source database Connection.
Step 2. Create a destination database Connection. Typically all the default settings work just fine depending on the type of database where you might consider disabling or enabling
Auto Commit . For example, if the target database is an SQL Server and you are planning to execute
TRUNCATE TABLE SQL within the transaction, it can lock the table for an extended period of time.
Step 3. Start creating a Flow by selecting
Database to database in the Gallery.
Step 4. Add a new source to destination transformation.
Step 5. If you want to
TRUNCATE all the records from the destination table before migrating the data from the source to the destination, follow the steps below; otherwise, go to step 6.
Mappingsand select the
DELETE SQLin the
Step 6. If you want to merge the existing data in the target with the data from the source, follow the steps below:
MERGE, or if native
MERGEis not supported, by the target database to
- In the
Lookup Fields, enter a name or names of the columns which uniquely identify the record. PostgreSQL and MySQL require a unique index on these columns.