How to migrate a PostgreSQL database into a SQLServer one

Question

I have a PostgreSQL database that I want to move to SQL Server -- both schema and data.I don't want to pay any money so I don't want to do very much work. Currently I'm doing this table by table, and there are about 100 tables to do. This is extremely tedious.

Is there some sort of trick that does what I want?

Answer 1

Hello @kartik,

The easier way to accomplish this.

First copy your table (or query) to a tab delimited file like so:

COPY (SELECT siteid, searchdist, listtype, list, sitename, county, street, 
   city, state, zip, georesult, elevation, lat, lng, wkt, unlocated_bool, 
   id, status, standard_status, date_opened_or_reported, date_closed, 
   notes, list_type_description FROM mlocal) TO 'c:\SQLAzureImportFiles\data_script_mlocal.tsv' NULL E''

Next you need to create your table in SQL, this will not handle any schema for you. The schema must match your exported tsv file in field order and data types.

Finally you run SQL's bcp utility to bring in the tsv file like so:your bcp MyDb.dbo.mlocal in "\\NEWDBSERVER\SQLAzureImportFiles\data_script_mlocal.tsv" -S trDBServer.database.windows.net -U YourUserName -P YourPassword -c

Hope this work!!
Thank You!!