About full refresh
Sometimes the simplest approach is the best one. If the source datasets are relatively small ~ 100K records (one hundred thousand) 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 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, but depending on the type of the database you might consider disabling or enabling auto-commit. For example, if the target database is a SQL Server and you are planning to execute TRUNCATE TABLE SQL within the transaction, it can lock the table for the 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 DELETE or 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.
5.1 Click the
MAPPING button, select the Parameters tab
5.2 Enter TRUNCATE or DELETE SQL in the Before SQL field.
Step 6. If you want to merge the existing data in the target with the data from the source follow the steps below:
6.1 Change Action to
MERGE, or if native MERGE is not supported by the target database to
6.2 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.