Append rows to the existing file

Overview

In Etlworks, you can append rows to the existing file.

Example

All Flows where the destination is a file always override the existing file, but there are instances when you need to modify the existing file by appending the rows. 

Process

Assuming that you already have a source Connection (and if needed, a Format) and a destination Connection and a Format:

Step 1. Create a Temporary database Connection.

Step 2. Start creating a Flow by selecting the Any to any ETL Flow type in the gallery. 

Step 3. Add a source-to-destination transformation that loads data from the existing file to the temporary table in the temporary database using a Connection created in step 1 as a TO Connection. You can enter any table name in TO, for example, staging. The table will be created on the fly and automatically dropped when the Flow finishes the execution. 

Step 4. Add a source-to-destination transformation where the source is the original source Connection (and, if needed, a Format) and the destination is a temp database Connection created in step 1. Enter the same table name in TO as you entered in step 3.

Step 5. Add the last source-to-destination transformation where:

• the source Connection is a temp database Connection created in step 1.
• the FROM is the same table name as the table named entered in TO in step 4.
• the TO Connection and Format are the original destination Connection and Format.
• the TO is the original destination filename. 

If you want to exclude duplicated rows created by merging data in the file with the data in the source, you can use the Source query, for example: select distinct * from staging.