Use this format to export data to a relational database (currently opened or external), for example, dBase, SQLite, Interbase, PostgreSQL, SQL Server etc. Export process is performed by means of the selected database engine (interface).
Notes
This format is available only in Exportizer Enterprise and (partially) Exportizer Pro; the latter one can export only to ODBC data sources.
You can export data to databases either from Exportizer GUI or from the 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).
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).
Table name - specify target table name.
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 description of the source table (if any), but it requires more time to process.
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. This option is 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 balanced value.
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.
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 can be achieved. It is recommended to use all sub-options for the best performance. 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.
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.
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. Note: If your exporting process fails with error message mentioning data size or data type, try to turn this option off and report this problem to the support team.
Batch mode - specify either to use batch mode or not (for FD connections only, which are available in Exportizer Enterprise). 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.
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.
Export mode
Specify export mode:
|
REPLACE+INSERT Target is created and filled with incoming rows; if target already exists, it is overwritten. |
|
CREATE_OR_REPLACE Blank target (using appropriate structure) is created; if target already exists, it is overwritten. |
|
APPEND Target is appended with incoming rows; if target does not exist, it is created. Notes |
|
EMPTY+INSERT Target is emptied before inserting incoming rows; if target does not exist, it is created. Notes |
|
UPDATE The records in the target that match incoming records, are replaced with incoming records. Notes |
|
APPEND+UPDATE The records in the target that match incoming records, are replaced with incoming records. Unmatched incoming records are appended to the target. Notes |
|
DELETE The records in the target that match incoming records, are deleted. Notes |
Note: For multi-table exporting, the value can be overridden for each individual table in Table Mappings section.
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 a file (table) without data exporting, use the corresponding Export mode instead.
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. The option is not available when exporting data to clipboard.
Export Formats
See also
Practical Case: Exporting Data to Microsoft Access
Practical Case: Exporting Data to Firebird
Practical Case: Exporting Data to Oracle