Exporting Data to SQL Server - Exportizer Documentation

Here you can find out how to export or migrate data to SQL Server database in Exportizer software.

Below, it is shown how to export data from GUI or command line.

Export Conditions

In most cases, to export data to SQL Server, the following conditions are required:

We recommend to use Exportizer Enterprise and FD interface when working with SQL Server databases (at least for target database):
- FD engine does not require ODBC drivers for working with SQL Server.
- If the target database is connected by FD, it is possible to use Batch mode export option, which can increase the speed of data exporting up to hundred times.
But if you prefer to use ODBC for both the source and target databases, Exportizer Pro can be used.
SQL Server client must be installed and its architecture (32-bit or 64-bit) must match Exportizer architecture.
All other involved components, e.g. ODBC drivers (if you use ODBC on source or target side), OLE DB providers etc. must also match Exportizer architecture.

Note: If your operating system is 64-bit, you can install both 32-bit and 64-bit versions of the Exportizer software and use them independently.

Exporting Preparation

Launch Exportizer Pro or Exportizer Enterprise.
Register your target SQL Server database. The recommended interface is FD, but other options are also available. Basically, you specify a server and a database. For local database, you specify localhost\<sql server instance name> for Server parameter. Note: You can register the target database from the Export dialog during the exporting.

Export Steps (GUI)

Preparing Source Data

Open the source database.
Choose a dataset or datasets to export:
- select a table from the table list;
  or
- open an SQL window, then write and execute an SQL query;
  or
- select multiple tables in the table list; before doing that, click Select Tables button ;
  or
- prepare a mix of multiple tables and/or SQL queries: open multiple table one by one and multiple SQL windows; in SQL windows, write your database queries and execute them to get the result sets.

Please note that exporting multiple datasets at a time is available in Exportizer Enterprise only.

Choosing Target Database and Export Options

Choose a needed item from Export menu. If you need to export one dataset from the current data window, you can click Export button . Export dialog will appear.
Switch to the Database tab. If this tab is not visible, click Favorite Export Formats button in the top-right corner of the window and make sure the Database item is selected.
Select your registered SQL Server database as a target database. If you did not register it yet, do it now by clicking the '...' button to the right.
Specify export parameters. Some recommendations:
- Turn on the Memory saving mode option and its related options. In case of exporting problems, if you suspect that they can be caused by these options, try different combinations of them. If exported data or column names on any side can contain Unicode characters, set the corresponding stream Encoding, e.g. UTF-8.
- Specify the Table name, i.e. name of the target table. For multi-table exporting, you can leave it empty, otherwise, all source datasets will be exported to one destination table.
- SQL Server supports optional table descriptions (comments). You can either type a description for the target table(s) or copy it from the source (if the source database supports table comments too and the source dataset is a table) by one click. Note that descriptions are applied during the target table creation only; they are ignored when the export mode is appending, updating, or deleting records in existing target table.
- Choose the needed Export mode. For multi-dataset exporting, this mode will be applied to the most of the tables, and you can override it for specific tables at the next (Table Mappings) step. For example, it can be Replace+Insert for the most of tables, and Update or Append+Update for others etc.
- Specify Commit interval. The bigger its value, the faster your exporting process. But too big value may cause memory and other issues. So, try to play with it (starting from a moderate value) to find the optimal value for your database before deploying the solution to your production environment.

Preparing Source-to-Target Mappings

Click Next to proceed to source-to-target table and/or column mappings or click Export to skip the next steps. When skipping the mappings step, all mappings are built automatically, which sometimes is not desired. When proceeding to the mappings step, initial mappings are built automatically too, but they can be edited when needed. It is recommended to use explicitly built mappings when:

table structure is complex;
new target columns must be added;
preparing a command line for data exporting;
exporting data to already existing tables.

So, when switching to the mappings step, there are two different scenarios depending on how many datasets are involved. If exporting more that one dataset, you work with table mappings, otherwise your work with field/column mappings.

In field mappings, you create the correspondence between the source and target columns. In addition to source table columns, you can also use calculated fields specified by formulas. It is possible to define full specifications for target columns here, i.e. rename target columns, choose their types, specify NOT NULL constraints, default values, primary key flags, computation formulas, descriptions (comments) etc. These specifications will be applied when the target table needs to be created or overwritten, i.e. when the corresponding export mode is used.

For SQL Server identity or computed columns, the source field or expression (left-most field mappings column) should be empty.

Editing column mappings for SQL Server database

In table mappings (for multi-dataset exporting), you create the correspondence between the source datasets (tables and/or queries) and target tables. Here, you can specify target table names, their descriptions (comments), and nested field mappings for each table pair.

Logs and Error Handling

Click Next to proceed to export logs and error handling options or click Export to start the export process immediately.

Resolving Export Performance Issues

In the case of the slow performance, there are some actions you can try:

First of all, make sure the target database is connected via FD interface. Using FD on the target side lets you utilize batch SQL commands execution which can speed up the exporting process drastically.
Try different combinations of Memory saving mode options and Commit interval setting. It is recommended to turn on the Memory saving mode, Use SQL parameters and Batch mode options and set the value of Commit interval from several hundreds to several thousands depending on you target server resources.
Finally, you might want to read all the data exporting recommendations.

Exporting to SQL Server from Command Line

You can create a command line for data migration to SQL Server using interface. To do that, make all the preparations described above, then click Tools button and choose Create Action File.... There, you can decide either to generate an action file or classic command line. Attention: Before generating the action file or command line, if you are using explicit mappings, save them to a file using Save button, otherwise you will get a shortened version of the mappings and loose many details.

In any case, you can use full command line specification to build the command line manually or to improve the automatically built one.

Alternative Scenario Using SQL

If you have some reason not to export data to SQL Server directly, Exportizer allows you to export the source data to SQL file(s) first, and then load data from the file(s) into the target database using Exportizer or an external SQL tool. And, of course, you can export data to SQL script either by GUI or from the command line. We recommend to use Batch Insert SQL statement type for that.

But please note, that when choosing exporting to SQL script, your export process will have two phases: first, exporting the data to SQL script; second, loading data from the script to your target database. So, this way can be less effective, especially for large datasets or when automating the data exporting. Another disadvantage of this way is that BLOB data will not be transferred.

Anyways, you can to try both export scenarios and select the fastest and/or the most convenient one.

See also