Database Tour Documentation
Migrate 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.

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

It is recommended to use FD interface in both cases.

Export Steps

1. Open the source database.
2. Switch to the Tables tab to the left. Right-click the table list and choose Multiple Selection.
3. Select the tables to export.
4. Click Export button.
5. Switch to the Database tab. From the list of registered databases, select the destination database. Specify needed export options (make sure to choose correct export mode). Leave the Table name field empty; otherwise, the data will be exported to one destination table and you must make sure all the source tables has the same or very similar structure.
6. Click Next and specify the source-to-target table mappings. You can skip this step by clicking Export instead of Next; in this case the correspondence between source and target tables will be done automatically according to table naming rules of the destination database.
7. Click Export.

In case of performance problems: try different combinations of Memory saving and Commit interval options; for large tables, use these recommendations.

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 /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.

/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.

<?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>
        <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>
            </Target>
          </FieldMapping>
        </Items>
      </FieldMappings>
    </TableMapping>
    <TableMapping>
      <Source>
        <TableName>detail.deal</TableName>
      </Source>
      <Target>
        <TableName>dbo.deal</TableName>
        <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

Command Line Usage: Export / Import Data

Table Mappings File

Action File