Is there a way to convert a postgres database for input to MS SQL Server?

Question

I have a schema and related postgres data on a unix box that a customer would like imported to MSSQL Server on the PC. Does anyone know a path would let me do this?

Answer 1

You'll need to add the postgres driver to the list in your Data Sources (ODBC).

From your control panel, select the Administrative Tools. You'll see the Data Sources (ODBC) entry. Select that and add a new entry in the System DSN tab for postgres. You should have the driver in the list shown when you select add.

If yours is like mine, on the configuration window, select "postgres" for Database. Keep the SSL mode "disable", the Server "localhost", the port will be "5432", the user name is probably "postgres", and use the password you gave your postgres user when you installed postgres on your PC. Select "test" to verify that it worked. Make sure that the postgres service is on or you'll get an error.



Now, from the SQL Server Enterprise Manager, create a new database. Use the "Run a Wizard" command specifying "Data Transformation Services==> DTS Import Wizard". In the Data Source list, select Other (ODBC Data Source). Use the "user/System DSN:" and select the PostgreSQL driver.



Next you'll be asked for a destination, you want to select the MS SQL Server on your local PC using the database name you just created. From here you'll be able to bring over whatever tables/views you want. It may take it a little while to load, but the data will be there.



During this, you should make sure the postgreSQL server is running (postmaster) as well as the MS SQL Server.



Good luck...

Answer 2

There are a ton of converter tools provided by MSSQL. You may need to convert the postgres into something more transient, like a text file with , delimiters for example. Install MSSQL Standard+ and you'll have tools that let you import data.







Edit: You will probably have to recreate the schema (i could be wrong). I'm not sure of a way to preserve this.

Answer 3

Use DTS in SQL Server for import data directly from Postgres.