8

I need to export all tables from SQL Server to PostgreSQL.

Try: I tried from SQL Server IDE but at some stage its giving the error about data types are different.

Question:How can I do export of data from SQL Server to PostgreSQL? Is COPY does my job? If yes, then how can I export all tables including records?

Sarfaraz Makandar
  • 5,933
  • 16
  • 59
  • 84
  • 2
    Export the data into a CSV/Text format and import it into Postgres using COPY (or any other tool that can process CSV/Text files) –  Jun 06 '14 at 09:34

5 Answers5

7

You can't export data from MSsql then import to PostgreSql because it is not same syntax, data type, but you can use tool to migration data from mssql to postgreSql, See more in topic migrate data from MS SQL to PostgreSQL?

Community
  • 1
  • 1
NuongNguyen
  • 126
  • 6
7

Use https://dbeaver.io/

  1. Create MS SQL and PostgreSQL database connections (login)
  2. Create target tables in PostgreSQL (same structures in MS SQL)
  3. F5 to see new tables
  4. Right-click on new tables -> 'Import Data' -> You will see 'Data Transfer' window
  5. Choose 'Table' type then click 'Next' -> You will see 'Select input object', where you can choose tables from MS SQL connection
  6. Just 'next' and check settings that you need, done :D
Dharman
  • 30,962
  • 25
  • 85
  • 135
An Le
  • 71
  • 1
  • 2
1

First export the schema into a file and run it against PostgreSQL until you've removed all incompatibilities.

You could try to do the same with the data you want to export but you may be better off writing a Python script to migrate it.

ruipacheco
  • 15,025
  • 19
  • 82
  • 138
1

There is an absolutely simple way using built-in SSIS tool using Management Studio. You can find the detailed answer here.

Ehsan Mirsaeedi
  • 6,924
  • 1
  • 41
  • 46
1

Use https://dbeaver.io/ , as An Le mentioned.

After 40 years of DB development, migrating DB data is still a challenge. DBeaver is a free tool to use for data migration. But you still have to migrate the schema.

Exporting data from DBeaver

From contextual menu of your SQLServer database or schema select Tools > Create new Task > Common > Data Export

You will generate SQL insert files or CSV files. For migration between database types use CSV files.

Cons of SQL Server Migration Tool

  1. Unable to migrate rows containing booleans. Export ended up in errors of migrationg data with Bool columns, complaining that value is not boolean, although both source and destination columns where of boolean type.

  2. Unable to continue with the next tables afer one table migration fails. SQL Server - A single error stops all migration even for tables that are not related to the initial error.

  3. Configuring the tool over and over again, trying to export your data is a waste of time. SQL Server migration task does not save the configuration of the source and destination connections. And the wizard is not user friendly, spending your time on it is frustrating. I assume the migration project was abandoned for at least 10 years.

profimedica
  • 2,716
  • 31
  • 41