Copy Data from PostgreSQL Direct to MSSQL Similar Table using Query

Question

I have a similar table in MSSQL that i am doing data migration to. PostgreSQL is live database and i wanted to migrate the new registration from PostgreSQL to MSSQL using script. I am using MSSQL Cursor currently but its tedious because i have to transfer the database from PostgreSQL to Excel then import to MSSQL using integrated services which is time wasting. I need a code that can query data from PostgreSQL and paste it directly into MSSQL.

I am using PostgreSQL 9 and MSSQL 2016

you can use my answer here: stackoverflow.com/questions/6563846/… — Simin Ghasemi
– Simin Ghasemi, Commented Dec 7, 2021 at 11:59

Joe Love · Accepted Answer · 2017-04-17 17:31:01Z

0

You have a couple of options:

first, you can use a programming language like PHP or python that simply selects * from the postgres table and inserts into the mssql.

Second, you could use a postgres "FDW" (Foreign data wrapper) to set up a database connection from the postgres db to the mysql db using ODBC. Once you get that set up, the FDW makes postgress just see the mysql table as if it were in the postgres db. Then, it's as simple as

insert into mssqltab select * from postgrestab;

Setting up the FDW connection isn't something I'll cover here as most of the config will be determined by how your db and tables are configured.

Of course the third option is not to migrate to mssql because postgresql is a better db, but that's just my opinion.

answered Apr 17, 2017 at 17:31

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Copy Data from PostgreSQL Direct to MSSQL Similar Table using Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related