Database Tour Documentation
Migrating Tables from PostgreSQL to SQL Server

This topic describes exporting a group of tables from PostgreSQL database to Microsoft SQL Server database in Database Tour.

Export Preparation

It is not mandatory but it is recommended to register both the source and the target databases before you start to configure your export process.

For speed reasons, it is recommended to use FD interface for both sides (i.e. for source and target databases).

Both databases must support the same architecture: 32-bit or 64-bit. I.e. one Database Tour instance must successfully open both databases.

Note: On our websites, you can find both versions of Database Tour: 32-bit and 64-bit. Please check which one you are downloading. If your operating system is 64-bit, you can install both Database Tour 32-bit and Database Tour 64-bit and use them independently. Please also note that one user license covers both 32-bit and 64-bit versions.

Export Steps

1. Open the source database.
2. You can export either selected tables or opened datasets of any kind.
   
   • Exporting selected tables
     
     1. Switch to the Tables tab to the left.
     2. Right-click the table list and choose Multiple Selection.
     3. Select tables to export.
     4. Click Export button or choose Tools | Export Data | Selected Tables... menu.
   • Exporting open datasets (you can use SQL queries results or a mix of queries and tables)
     
     1. Open multiple SQL windows, type SQL queries there and execute them to open data. Open needed tables too and switch to their Data pages.
     2. Choose Tools | Export Data | Open Datasets... menu.
     3. Choose the datasets to export and click OK.
3. 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.
4. From the list of registered databases, select the destination database and specify export parameters. Some notes:
   • 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 source data and/or column names on any side may contain Unicode characters, make sure to choose the correct SQL stream Encoding, e.g. UTF-8.
   • Choose the Export mode to be applied to the most of the tables. You can override it for specific tables at the next step. For example, it can be Replace+Insert for the most of the target tables, Update for some of the tables, Delete for others etc.
   • Leave the Table name field empty; otherwise, the data will be exported to one destination table.
   • The bigger Commit interval value, the faster your exporting process. But too big value may cause memory and other issues. Therefore, try to play with it to find the optimal value before porting the solution to your production environment.
   
5. Click Next and specify the source-to-target table mappings, i.e. the correspondence between source datasets and target tables.

   Here, it is possible to specify full column specifications for target tables, and override Export mode for each table (if you need different export modes for different tables).

   PostgreSQL and SQL Server support table and column descriptions (comments), and you can specify them here; use <copy_from_source> macro to force the application to copy the descriptions from the source tables (if any) to target tables. Note that descriptions are applied during the table creation only; they are ignored when the record mode is appending, updating, or deleting records in existing target table(s).

   You can skip this step by clicking Export instead of Next; in this case the table mappings will be built implicitly according to the naming rules of the destination database.

6. Click Export to begin the export procedure. You can interrupt the process by pressing ESC or by clicking Cancel in additional window which is shown during the exporting process.

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.
• Read recommendations for exporting large datasets and exporting average datasets.

Migrate Data via Command Line

Notes

• This functionality is available in Database Tour Pro.
• A basic command line can be generated automatically by choosing Tools | Build Command Line | Export / Import Data... menu.
• The basic command line and example below can be improved by adding other command line switches and parameters according to the documentation.
• The example below can be transformed to an action file, which is much more comfortable to work with.

The command line for exporting multiple tables from PostgreSQL to SQL Server looks like this:

dbtour.exe /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /CommitInterval=200 /SrcDBInterface=FD /SrcDBKind=DSN /SrcDBDriver=POSTGRESQL /SrcServer=MyPostresDbServer.com /SrcPort=5432 /SrcDB=clients "/SrcVendorLibrary=C:\Program Files (x86)\PostgreSQL\10\bin\libpq.dll" /SrcDBUserName=stage /SrcDBPassword=stage_password /TrgDBInterface=FD /TrgDBKind=DSN "/TrgDBDriver=SQL SERVER" /TrgServer=127.0.0.1\SQLEXPRESS /TrgDB=prod /TrgDBUserName=sa /TrgDBPassword=sa_password /TableMappingsFile=c:\DWH\Export\TableMappings.xml /MemorySaving /UseSQLParameters /UseBatchMode /Encoding=UTF-8 /LogFile=C:\DWH\Log\export_req.log /AppendLog

Description of used command line options:

/silent

Tells the application to perform an operation in silent mode, i.e. without any windows.

/export

Tells the application to perform an export operation.

/ExportType=DATABASE

Specifies that the data will be exported to a database.

/ExportMode=REPLACE+INSERT

Specifies that the target tables will be created (or replaced when already exist), and the source data will be inserted in the target tables.

/CommitInterval=200

Forces the application to issue Commit after exporting every 200 records.

/SrcDBInterface=FD

Interface to open the source database is FD.

/SrcDBKind=DSN

Data source kind of the source database is a general database.

/SrcDBDriver=POSTGRESQL

Driver (database type) of the source database is PostgreSQL.

/SrcServer=MyPostresDbServer.com

Specifies the server name or IP address of the source database.

/SrcPort=5432

Specifies port for the source database.

/SrcDB=clients

Specifies the name of the source database.

/SrcVendorLibrary=C:\Program Files (x86)\PostgreSQL\10\bin\libpq.dll

Specifies vendor library for the source database.

/SrcDBUserName=stage

Specifies username for the source database.

/SrcDBPassword=stage_password

Specifies user password for the source database.

/TrgDBInterface=FD

Interface to open the target database is FD.

/SrcDBKind=DSN

Data source kind of the target database is a general database.

/SrcDBDriver=SQL SERVER

Driver (database type) of the target database is Microsoft SQL Server.

/TrgServer=127.0.0.1\SQLEXPRESS

Specifies the server name or IP address of the target database.

/TrgDB=prod

Specifies the name of the target database.

/TrgDBUserName=sa

Specifies username for the target database.

/TrgDBPassword=sa_password

Specifies user password for the target database.

/TableMappingsFile=c:\DWH\Export\TableMappings.xml

Specifies a file with table mappings. Table mappings contain a list of mappings where each source table maps to the corresponding target table. See example of the table mappings file below.

/MemorySaving

Instructs the application to use memory saving mode.

/UseSQLParameters

Instructs the application to use SQL parameters for memory saving mode.

/UseBatchMode

Instructs the application to use batch mode for memory saving mode.

/encoding=UTF-8

Specifies SQL stream encoding for memory saving mode.

/LogFile=C:\DWH\Log\export_req.log

Specifies a file where the export log will be written.

/AppendLog

If the log file is not empty, the current export logs will be appended to the end of it and the previous logs will be kept.

Example of PostgreSQL to SQL Server Table Mappings File

The table mappings file contains the correspondence between source and target table names. Optionally, each table pair can include nested field mappings, i.e. the correspondence between source and target columns. The following example contains the table mappings for three table pairs. Each of them contains nested field mappings. It's up to you to provide such mappings, but we recommend to do it.

You can create such files either in the Export dialog on the Table Mappings step or manually using a text editor.

<?xml version="1.0" encoding="UTF-8"?>
<!-- Source-to-Target table mappings for clients and deals -->
<TableMappings>
  <Items>
    <TableMapping>
      <Source>
        <TableName>detail.client</TableName>
      </Source>
      <Target>
        <TableName>dbo.client</TableName>
        <TableDescription>Clients</TableDescription>
        <RecordLogFileName></RecordLogFileName>
        <BadRecordLogFileName></BadRecordLogFileName>
      </Target>
      <FieldMappings>
        <Items>
          <FieldMapping>
            <Source>
              <FieldName>client_id</FieldName>
              <FieldType>Integer</FieldType>
            </Source>
            <Target>
              <FieldName>client_id</FieldName>
              <FieldType>int</FieldType>
              <FieldNotNull>1</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>INT</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>first_name</FieldName>
              <FieldType>WideString</FieldType>
            </Source>
            <Target>
              <FieldName>first_name</FieldName>
              <FieldType>nvarchar</FieldType>
              <FieldSize>128</FieldSize>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>NVARCHAR(128)</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>last_name</FieldName>
              <FieldType>WideString</FieldType>
            </Source>
            <Target>
              <FieldName>last_name</FieldName>
              <FieldType>nvarchar</FieldType>
              <FieldSize>128</FieldSize>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>NVARCHAR(128)</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>middle_name</FieldName>
              <FieldType>WideString</FieldType>
            </Source>
            <Target>
              <FieldName>middle_name</FieldName>
              <FieldType>nvarchar</FieldType>
              <FieldSize>128</FieldSize>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>NVARCHAR(128)</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>birth_date</FieldName>
              <FieldType>Date</FieldType>
            </Source>
            <Target>
              <FieldName>birth_date</FieldName>
              <FieldType>datetime</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>DATETIME</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>passport</FieldName>
              <FieldType>WideString</FieldType>
            </Source>
            <Target>
              <FieldName>passport</FieldName>
              <FieldType>nvarchar</FieldType>
              <FieldSize>64</FieldSize>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>NVARCHAR(64)</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>sex_id</FieldName>
              <FieldType>WideString</FieldType>
            </Source>
            <Target>
              <FieldName>sex_id</FieldName>
              <FieldType>char</FieldType>
              <FieldSize>1</FieldSize>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>CHAR(1)</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>created</FieldName>
              <FieldType>TimeStamp</FieldType>
            </Source>
            <Target>
              <FieldName>created</FieldName>
              <FieldType>datetime</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>DATETIME</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>no_middle_name</FieldName>
              <FieldType>Boolean</FieldType>
            </Source>
            <Target>
              <FieldName>no_middle_name</FieldName>
              <FieldType>bit</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>BIT</FieldSQLSpecification>
              <FieldDescription>Sign that the client does not have a middle name</FieldDescription>
            </Target>
          </FieldMapping>
        </Items>
      </FieldMappings>
    </TableMapping>
    <TableMapping>
      <Source>
        <TableName>detail.deal</TableName>
      </Source>
      <Target>
        <TableName>dbo.deal</TableName>
        <TableDescription>Client deals</TableDescription>
        <RecordLogFileName></RecordLogFileName>
        <BadRecordLogFileName></BadRecordLogFileName>
      </Target>
      <FieldMappings>
        <Items>
          <FieldMapping>
            <Source>
              <FieldName>deal_id</FieldName>
              <FieldType>LargeInt</FieldType>
            </Source>
            <Target>
              <FieldName>deal_id</FieldName>
              <FieldType>BIGINT</FieldType>
              <FieldNotNull>1</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>BIGINT</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>state_id</FieldName>
              <FieldType>SmallInt</FieldType>
            </Source>
            <Target>
              <FieldName>state_id</FieldName>
              <FieldType>SMALLINT</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>SMALLINT</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>client_id</FieldName>
              <FieldType>Integer</FieldType>
            </Source>
            <Target>
              <FieldName>client_id</FieldName>
              <FieldType>INTEGER</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>INTEGER</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>creation_date</FieldName>
              <FieldType>Date</FieldType>
            </Source>
            <Target>
              <FieldName>creation_date</FieldName>
              <FieldType>DATETIME</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>DATETIME</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>deal_number</FieldName>
              <FieldType>WideString</FieldType>
             </Source>
            <Target>
              <FieldName>deal_num</FieldName>
              <FieldType>NVARCHAR</FieldType>
              <FieldSize>25</FieldSize>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>NVARCHAR(25)</FieldSQLSpecification>
             </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>close_date</FieldName>
              <FieldType>Date</FieldType>
            </Source>
            <Target>
              <FieldName>close_date</FieldName>
              <FieldType>DATETIME</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>DATETIME</FieldSQLSpecification>
            </Target>
          </FieldMapping>
        </Items>
      </FieldMappings>
    </TableMapping>
    <TableMapping>
      <Source>
        <TableName>detail.payment</TableName>
      </Source>
      <Target>
        <TableName>dbo.payment</TableName>
        <RecordLogFileName></RecordLogFileName>
        <BadRecordLogFileName></BadRecordLogFileName>
      </Target>
      <FieldMappings>
        <Items>
          <FieldMapping>
            <Source>
              <FieldName>payment_id</FieldName>
              <FieldType>Integer</FieldType>
            </Source>
            <Target>
              <FieldName>payment_id</FieldName>
              <FieldType>INTEGER</FieldType>
              <FieldNotNull>1</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>INTEGER</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>payment_date</FieldName>
              <FieldType>Date</FieldType>
            </Source>
            <Target>
              <FieldName>payment_date</FieldName>
              <FieldType>DATETIME</FieldType>
              <FieldNotNull>1</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>DATETIME</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>deal_id</FieldName>
              <FieldType>LargeInt</FieldType>
            </Source>
            <Target>
              <FieldName>deal_id</FieldName>
              <FieldType>BIGINT</FieldType>
              <FieldNotNull>1</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>BIGINT</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>payment_amount</FieldName>
              <FieldType>BCD</FieldType>
            </Source>
            <Target>
              <FieldName>payment_amount</FieldName>
              <FieldType>FLOAT</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue></FieldDefaultValue>
              <FieldSQLSpecification>FLOAT</FieldSQLSpecification>
            </Target>
          </FieldMapping>
          <FieldMapping>
            <Source>
              <FieldName>ts</FieldName>
              <FieldType>TimeStamp</FieldType>
            </Source>
            <Target>
              <FieldName>ts</FieldName>
              <FieldType>DATETIME</FieldType>
              <FieldNotNull>0</FieldNotNull>
              <FieldDefaultValue>GetDate()</FieldDefaultValue>
              <FieldSQLSpecification>DATETIME</FieldSQLSpecification>
            </Target>
          </FieldMapping>
        </Items>
      </FieldMappings>
    </TableMapping>
  </Items>
</TableMappings>

See also

• Opening PostgreSQL Databases
• Opening SQL Server Databases
• Exporting Data
• Database Export Format
• Command Line Usage: Export / Import Data
• Table Mappings File
• Action File