MS SQL Server to PostgreSQL Quick Copy

Published on 12/8/2014

You can quickly copy data from an MS SQL Server database to PostgreSQL using the bcp command, iconv, and psql’s \copy command.

I copied 100,000 records from one server, to my laptop, to another server in 1.5 seconds.

Sadly, bcp must write to a file first, so you can’t pipe it directly. Delete the file when you’re done.

In Linux, you could create a named pipe and pretend it’s a file, using the mkfifo command.

bcp can only output Unicode in UTF-16, so we must use iconv to convert the output to UTF-8

I assume that you have psql installed, and that it’s configured to connect to your PostgreSQL server.

Windows

If bcp is not installed (run bcp.exe -v) then install the Microsoft Command Line Utilities 11 for SQL Server
If win_iconv is not installed, download it, rename it to iconv.exe and put it in your PATH

Linux

If you have not installed the Microsoft ODBC Driver for SQL Server on Linux, you can get it here

In your shell run (this assumes you are using ActiveDirectory/Kerberos auth. Use -U, -P instead of -T if not):

bcp "select * from some_table" queryout results.tsv -T -S serverHostName -w

then:

iconv -f UTF16 -t UTF8 results.tsv | psql -c "\copy some_table from STDIN"

Delete results.tsv

... views