Re: Read MS-SQL data into Postgres via ODBC link? - Mailing list pgsql-general

I was really hoping to keep the data-replication (between MSSQL --> PG)
contained within a PG function.

Instead I could write a small shell script or C service to do this using
tsql (freetds). I have access to the MSSQL data via unixodbc and
tdsodbc/freetds in my Ubuntu console.

But I want to read from that ODBC stream directly in Postgresql, like a
SELECT on Linked Servers in MSSQL-world or on a linked table in MSACCESS.
That would give control over the ODBC interaction to the PG function rather
than a non-DB entity.

Has anyone tried ODBC-Link
(http://www.cybertec.at/en/postgresql_products/odbc-link)? I couldn't get
through the install due to Ubuntu-related error.



-----Original Message-----
From: Alban Hertroys [mailto:dalroi@solfertje.student.utwente.nl]
Sent: Tuesday, July 05, 2011 7:12 PM
To: Susan Cassidy
Cc: JB@BlackSkyTech.com; pgsql-general@postgresql.org; 'Brent Wood'
Subject: Re: [GENERAL] Read MS-SQL data into Postgres via ODBC link?

On 5 Jul 2011, at 23:27, Susan Cassidy wrote:

> >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
>
> Just connect to both databases (in Perl or Python, or something), do a
select on the MSSQL data on the MSSQL handle.  When you get the row into a
set of column variables, just insert the columns via the Postgres handle
into the Postgres database.  Simple as pie.  You can prepare the INSERT
statement so that multiple executions use bind variables.  Commit every 1000
rows or whatever.

For those queries you will need a similarly prepared Postgres equivalent of
the database schema of course, and quite possibly some conversions from
MS-SQL types to Postgres types. It's quite obvious, but mentioning it
doesn't hurt anyone, and there could be some caveats in that process. Both
databases are fairly SQL compliant though, it's probably an easier
conversion than from the mistakenly assumed MySQL from earlier replies :P

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:1276,4e139a6212098298070230!