For INSERT option, table must be empty

Symptoms

If we import only data into existing objects, for example, when we migrate large objects and we divide the import into several parts, as described in the article How to Optimize Migration of Large Tables, then during the import process new data is not added to the existing table because of error appears:

Cause

This happens because our tool creates an INSERT statement by default in the control file (.ctl format), which contains instructions for loading data:

Solution

To resolve the issue with partial data in, we recommend doing manually the following actions and making sure everything is working:

1. For the stage of importing a separate part of the data into the table, you should set these options (remove automatic import and determine what to import) and click the Start Conversion button:

2. Go to the EXPORT directory (you can find it by clicking “Export Directory” at Migration Execution page):

3. Change “INSERT” statement to “APPEND” in .ctl file:

In case of multiple tables conversion we recommend using “Find in files” command and the following regular expression ( ^INSERT(?=\s*\nFIELDS) ) in Notepad++:

4. Then you can Start Import of the data in the tool.

If you have any additional questions, please feel free to contact us: support@ispirer.com