Online Documentation for SQL Manager for SQL server

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 column(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 columns. This column (or columns) is used as key column 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 / Bulk 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 Bulk insert mode uses native commands for a particular server: BULK INSERT for Microsoft® SQL Server™. 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.

 

CheckBox Keep identity

This option specifies that the values for an identity column are present in the file being imported.

 

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 columns of the table to be used as the key columns for the import process.

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

 

 

Click the Next button to proceed to the Customizing common options step or to the Customizing bulk insert options step of the wizard if you have selected Bulk insert as the import type.