Re: Read MS-SQL data into Postgres via ODBC link?

From: Jonathan Brinkman
Subject: Re: Read MS-SQL data into Postgres via ODBC link?
Date: ,
Msg-id: 004201cc3b22$adeae100$09c0a300$@com
(view: Whole thread, Raw)
In response to: Re: Read MS-SQL data into Postgres via ODBC link?  ("Brent Wood")
Responses: Re: Read MS-SQL data into Postgres via ODBC link?  (Susan Cassidy)
List: pgsql-general

Tree view

Read MS-SQL data into Postgres via ODBC link?  ("Jonathan Brinkman", )
 Re: Read MS-SQL data into Postgres via ODBC link?  ("Brent Wood", )
  Re: Read MS-SQL data into Postgres via ODBC link?  ("Jonathan Brinkman", )
   Re: Read MS-SQL data into Postgres via ODBC link?  (Susan Cassidy, )
    Re: Read MS-SQL data into Postgres via ODBC link?  (Alban Hertroys, )
     Re: Read MS-SQL data into Postgres via ODBC link?  ("Jonathan Brinkman", )
      Re: Read MS-SQL data into Postgres via ODBC link?  (Ivan Sergio Borgonovo, )
 Re: Read MS-SQL data into Postgres via ODBC link?  (Sim Zacks, )
  Re: Read MS-SQL data into Postgres via ODBC link?  (Alban Hertroys, )

Thanks

I’m importing into Postgresql 8.4.8 from MSSQL 2005.

 

I do not have control over the MSSQL server, it is at a customer’s site. I only have access to read-only views on their server, from which I replicate the data to my postgres staging tables.

 

I cannot have the MSSQL server do anything like create .csv files, since it’s not my server and the customer will not do that kind of thing (or create DTS scripts, etc. from the PUSH side).

 

I’m hoping there is a way I can simply SELECT from an ODBC source and use that SELECT as part of a Postgres INSERT statement.

 

I don’t use MySQL for anything.

 

Thanks much for your response!

 

J

 

From: Brent Wood [mailto:]
Sent: Monday, July 04, 2011 8:58 PM
To:
Cc:
Subject: Re: [GENERAL] Read MS-SQL data into Postgres via ODBC link?

 

Hi Jonathan,

I haven't done this from MySQL, but have from Postgres & from Oracle

From a command line client, extract the data from the source table, so you get a stream of csv style records.
Then pipe these directly into a psql statement to load them into the target table.

A simple pg2pg example:

psql -d $DB1 -F '|' -Atc "select * from table;" | psql -d $DB2 -c "copy table from STDIN with delimiter '|' with null '';"

A MySQL example would be similar, the second part pretty much identical.


HTH,

  Brent Wood



Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand


>>> "Jonathan Brinkman" <> 07/05/11 12:45 PM >>>
Greetings

I'd like to INSERT data into my Postgresql 8.4.8 table directly from an
ODBC-linked MS-SQL table or view.

I'm looking at using the Cybertec tool "ODBC-LINK"
(http://www.cybertec.at/en/postgresql_products/odbc-link) but I'm wondering
if there isn't a way to do this through Postgresql directly?

I saw a post about someone doing a "SELECT * FROM XXX ODBC SOURCE" or
something like that
(http://archives.postgresql.org/pgsql-odbc/2009-07/msg00032.php) and that
would be cool. I don't need to import massive datasets, only 20-30K records
at a time.

Thanks much

Jonathan



--
Sent via pgsql-general mailing list ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Please consider the environment before printing this email.

NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.


pgsql-general by date:

From: c k
Date:
Subject: Re: [ADMIN] Python UCS4 error
From: John R Pierce
Date:
Subject: Re: [ADMIN] Python UCS4 error