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

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