Online Documentation for SQL Manager for MySQL

Setting import mode


This step of the wizard allows you to define the records processing mode as Insert All, Insert New, Update, Update or Insert, Delete, Delete or Insert mode:

  • Insert all: all records from the source file are inserted into the tables irrespective of whether any records exist in the destination table or not
  • Insert new: already existing records are skipped, and new records are inserted into the destination table
  • Update: all existing records are updated from the source file
  • Update or insert: already existing records are updated and new records are inserted into the destination table
  • Delete: already existing records are deleted
  • Delete or insert: existing records are deleted and new records are inserted into the destination table

 

Import Data - Setting import mode

 

 

Here is an example of some import modes offered by Import Data Wizard:

 

All import modes (except for the Insert All mode) are based on key values information. In order to perform import operations with these modes used, you need to have matches between the source file key column(s) and the destination table key field(s).

For example, your source file contains three rows with the key values 1, 2, 3, and your destination table contains three rows with the key values 1, 2, 4.

 

Destination table

Source file data

Import Data - Setting import mode - Destination Table

Import Data - Setting import mode - Source Table

 

If you use the Insert new import mode, in this case only the row with key value 3 will be inserted into the destination table.

If you use the Update import mode, then the rows with key values 1, 2 will be updated.

If you use the Update or insert import mode, then rows 1, 2 will be updated and the row with key value 3 will be inserted.

It is applied to all other import modes, except for the Insert all mode. For all these modes (except for the Insert all mode) it is obligatory to select the primary key fields. This field (or fields) is used as key field to identify specific data in the target database.

 

Insert new

Update

Update or insert

Delete

Delete or insert

Import Data - Setting import mode - Result - Insert new

Import Data - Setting import mode - Result - Update

Import Data - Setting import mode - Result - Update or insert

Import Data - Setting import mode - Result - Delete

Import Data - Setting import mode - Result - Delete or insert

 

The key columns for these operations are defined in the Key columns area.

 

 

Single commands / Universal mode / Batch insert type

The Single commands import mode is performed with the Single Commands method used and serves to generate and execute single SQL commands on the server, whereas the Batch insert mode uses native MySQL commands to import a data set as a batch. With the help of the Single commands import mode your data can be imported considerably faster as compared to the Universal mode which is used for backward compatibility.

 

Use Import mode to select whether to insert all records, or to update/delete existing ones. Note that for updating/deleting existing records in the target table you should move its key columns from the Available columns list to the Selected columns list.

 

 

The Key columns area allows you to select the fields of the table to be used as the key fields for the import process.

To select a field, you need to move it from the Available columns list to the Selected columns list. Use the Move all to Selected Move to Selected Move to Available Move all to Available buttons or drag-and-drop operations to move the fields from one list to another.

 

 

When you are done, click the Next button to proceed to the Customizing common options step of the wizard.