Use this format to export data to a relational database (currently opened or external), for example, dBase, SQLite, Interbase, Firebird, PostgreSQL, Oracle etc. Export process is performed by means of the selected database engine (interface).
The data can be exported to databases either from Database Tour (Pro) GUI or from the Database Tour Pro command line.
When exporting data from GUI, switch to Database page of the Export dialog. If this page is not visible, click Favorite Export Formats button in the top-right corner of the window and make sure the corresponding format is selected.
Format Specific Options
Database - specify a database to export data to. You either select a registered database from the drop-down list or click '...' button to the right and register the new database.
User name - specify user name to connect target database (can be left blank, in this case application may ask it automatically when trying to connect the target database). Command line equivalent: /TrgDBUserName.
Password - specify password to connect target database (can be left blank, in this case application may ask it automatically when trying to connect the target database). Command line equivalent: /TrgDBPassword.
Table name - specify target table name. Command line equivalent: /TrgTableName.
Table description - specify table description to be applied to the target table; currently, it can be applied to Oracle, SQL Server, PostgreSQL, and MySQL databases, and only to table, which is created during the export operation. If you specify <copy_from_source>, the application will try to use the description of the source table (if any) extracting it during the exporting, but it will require more time to process. When exporting one table, you can extract the source description (if any) using Copy from Source button. Command line equivalent: /TrgTableDescription.
Commit interval - specify number of source records that should be exported before the transaction in target database is committed; if 0 is specified, commit is done after exporting all records. Too small value can slow up the exporting process, but too large value can cause memory overflow errors; the normal value usually lays between several hundred and several thousand depending on your environment. Ignored if target database does not support transactions. Note: The bigger this value, the faster is exporting process; but too large value can cause problems on unstable network or cause the process to stop due to lack of memory on server side, so try to find the optimized value (it will depend on your server resources and network quality). Command line equivalent: /CommitInterval.
Trim trailing spaces - specify either to trim trailing spaces and control characters in target or not. Applicable for char and varchar data only. Using this option allows to remove useless data and thus reduce the output volume, and sometimes improve the target look. Command line equivalent: /TrimTrailingSpaces.
Memory saving mode - specify either to use memory saving mode or not. The memory saving mode is especially useful when the source or target tables have large number of records. This mode internally creates and executes series of SQL statements instead of opening the target dataset. In addition to memory saving, the improvements in performance may be achieved. It is recommended to use all sub-options for maximum speed. Note: This mode may work differently for different target database types/interfaces, therefore it is recommended to test it carefully before using on production systems. Try to play with this option and dependent options to find their optimal combination. Command line equivalent: /MemorySaving.
Encoding - specify inner SQL stream encoding for memory saving mode. For example, if your data and/or field/column names contain Unicode characters, you need to choose UTF-8 encoding etc. Command line equivalent: /Encoding.
Use SQL parameters - specify either to use SQL parameters in memory saving mode or not. Using parameterized SQL queries can significantly speed up the process and allows to export BLOB data for most database types/interfaces. Command line equivalent: /UseSQLParameters.
Batch mode - specify either to use batch mode or not (for FD connections only). Batch mode reduces the target database workload and increases the speed of export drastically. The size of a batch equals to Commit interval value. Note: The record level logging may work differently in this mode. Command line equivalent: /UseBatchMode.
Key fields (moved to Field mappings section) are used to match records for UPDATE, APPEND+UPDATE, or DELETE export modes. This must be a name of a source field or a comma-separated list of several source field names. In Field mappings section, the key fields must be present in the mappings list. Command line equivalent: /KeyFields.
Export mode
Specify export mode:
|
REPLACE+INSERT Target table is created and filled with incoming rows; if the target table already exists, it is overwritten. |
|
CREATE_OR_REPLACE Blank target table (using appropriate structure) is created; if the target table already exists, it is overwritten. |
|
APPEND Target table is appended with incoming rows; if the target table does not exist, it is created. Notes |
|
EMPTY+INSERT Target table is emptied before inserting incoming rows; if the target table does not exist, it is created. Notes |
|
UPDATE The records in the target table that match incoming records, are replaced with incoming records. Notes |
|
APPEND+UPDATE The records in the target table that match incoming records, are replaced with incoming records. Unmatched incoming records are appended to the target. Notes |
|
DELETE The records in the target table that match incoming records, are deleted. Notes |
Note: For multi-table exporting, the value can be overridden for each individual table-to-table pair in Table mappings section.
Command line equivalent: /ExportMode.
Record range
Specify range of source records to be exported:
- Full table - all records are exported.
- Selected records only - only selected records are exported. To select rows, click the corresponding button and then select needed rows using Shift, Ctrl and arrow keys.
- From current record to the last one - all data between current and the last records are exported.
Limit the record count to - specify a maximum number of records to be exported. If this option is not specified or it is less then 1, all records from the specified record range will be exported. If you just want to create an empty table without data exporting, use the corresponding Export mode instead. Command line equivalent: /LimitRecordCount.
Column range
Specify range of source columns to be exported:
- All columns - all columns (including columns, which were temporary hidden by user) are exported.
- Selected column only - only selected (current) column is exported.
- Visible columns - only visible columns are exported.
Other options
Ask before overwrite or empty existing target - specify either to ask the user to overwrite existing target for REPLACE+INSERT and CREATE_OR_REPLACE export modes. Reverse command line equivalent: /SuppressOverwriteOrDeletePrompt.
See also
Practical Case: Export Data from DBF to Paradox
Practical Case: Export Data from Excel to SQLite
Practical Case: Migrate Tables from PostgreSQL to SQL Server