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
- Open the source database.
- Switch to the Tables tab to the left. Right-click the table list and choose Multiple Selection.
- Select the tables to export.
- Click Export button.
- 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.
- 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.
- 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