Microsoft SQL Server and PostgreSQL are both widely used, cutting-edge DBMSs. They also provide excellent APIs and management consoles for developers. Everybody who looks at the licensing terms and conditions of both systems will see that Microsoft SQL is more restricted and has a higher total cost of ownership than PostgreSQL. As a result, it’s no wonder that PostgreSQL server has become the database of choice for so many companies and organizations.
The following procedures must be followed in order to transfer data across databases managed by different systems:
- Exporting table definitions as DDL statements from the source database; converting and loading the DDL statements into the target database.
- Database objects, such as views, stored procedures, and triggers, are extracted as SQL statements and source code; these are then converted into PostgreSQL format and loaded into the target database.
- Database objects, such as views, stored procedures, and triggers, are extracted as SQL statements and source code; these are then converted into PostgreSQL format and loaded into the target database.
To elaborate on each of these measures. To convert table definitions in Microsoft SQL Server to DLL statements, follow these steps:
- Right-clicking a database in SQL Server Management Studio will bring up a context menu; from there, pick Tasks, then Generate Scripts (in SQL Server 2008 and prior). Follow the prompts and activate the “data” checkbox (it’s set to false by default).
- To generate scripts, right-click a database in SQL Management Studio (versions 2012 and later) and choose “Tasks” from the resulting menu.
- Select the appropriate “Types of data to script” in the “Set scripting settings” tab’s “Advanced” section, and then modify the generated script to conform to PostgreSQL syntax before loading it into the target database.
- Change square brackets around names to double quotes and remove them around types; eliminate MS SQL-specific syntax patterns like “SET ANSI NULLS ON”, “SET QUOTED IDENTIFIER ON”, “SET ANSI PADDING ON”, etc.
- Swap out MS SQL’s default “dbo” schema with “public” (in PostgreSQL)
- get rid of any optional keywords like “WITH NOCHECK”, “CLUSTERED”, etc. that aren’t supported by PostgreSQL.
- Replace “INT IDENTITY(…)” with “SERIAL”, “DATETIME” with “TIMESTAMP”, “MONEY” with “NUMERIC(19,4)”, etc. using types allowed by PostgreSQL
- change all occurrences of “GO” in SQL Server queries to their PostgreSQL counterpart “;”
The moment has come to convert MS SQL data. Even this process is managed by Microsoft SQL Management Studio: To export data, choose the database you want to export from, right-click on its name, and choose the appropriate option from the Tasks menu. Select “Microsoft OLE DB Provider for SQL Server” as the data source and “Flat File Destination” as the destination in the wizard. Following these instructions, your data will be saved in the.csv file you choose.
If your table also contains binary data, this method may not work, and you’ll need to find a solution. To implement the workaround, choose “Write a query to specify the data to transfer” item on the “Specify Table Copy or Query” wizard page. Create the following query on the “Provide a Source Query” tab, changing all binary columns to varchar(max)as follows:
Create the following query on the “Provide a Source Query” tab, changing all binary columns to varchar(max)as follows: select <field 1>, …, cast (master.sys.fn_varbintohexstr(cast(<binary field > as varbinary(max))) as varchar(max)) as <new field name> from <table name>;
The output csv file may be imported into PostgreSQL table through “COPY” command:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
This little tutorial shows that transferring data from Microsoft SQL Server to PostgreSQL is a time-consuming and difficult task. With the right tools, however, migrating a database is straightforward and fully automated.
One such program is Intelligent Converters’ SQL Server to PostgreSQL. It combines an intuitive user interface with a robust set of features, and it converts data quickly and efficiently.
- Microsoft SQL data can be merged into an existing PostgreSQL database.
- Data can be exported from a SQL Server database into a PostgreSQL script in the event that a direct connection to a PostgreSQL server is unavailable.
- Microsoft SQL data can be merged into an existing PostgreSQL database.
- Data can be exported from a SQL Server database into a PostgreSQL script in the event that a direct connection to a PostgreSQL server is unavailable.
Just like all of Intelligent Converters’ other products, the SQL to PostgreSQL converter is available in a limited-features free trial edition. The one-year subscription to updates and unlimited support included with the registered version of the software is great value.
