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

Read MS-SQL data into Postgres via ODBC link?

From
"Jonathan Brinkman"
Date:
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



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

From
"Brent Wood"
Date:
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" <JB@BlackSkyTech.com> 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 (pgsql-general@postgresql.org)
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.

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

From
Sim Zacks
Date:
I've done similar things with a plpythonu function.

Basically, import the mysql module, call your select statement and then
for each row do a plpy.execute(insert stmt)


Sim


On 07/05/2011 12:10 AM, Jonathan Brinkman wrote:

> 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
>
>
>


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

From
Alban Hertroys
Date:
Guys, the OP isn't using MySQL, but MS-SQL.
Not that your solutions don't apply in that case, but it's just a little sloppy to be talking about the wrong database
allthe time ;) 

On 5 Jul 2011, at 9:37, Sim Zacks wrote:

> I've done similar things with a plpythonu function.
>
> Basically, import the mysql module, call your select statement and then for each row do a plpy.execute(insert stmt)
>
>
> Sim
>
>
> On 07/05/2011 12:10 AM, Jonathan Brinkman wrote:
>
>> 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 (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Alban Hertroys

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


!DSPAM:737,4e133bd412094272933627!



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

From
"Jonathan Brinkman"
Date:

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:b.wood@niwa.co.nz]
Sent: Monday, July 04, 2011 8:58 PM
To: JB@BlackSkyTech.com
Cc: pgsql-general@postgresql.org
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" <JB@BlackSkyTech.com> 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 (pgsql-general@postgresql.org)
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.

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

From
Susan Cassidy
Date:

>From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jonathan Brinkman
>Sent: Tuesday, July 05, 2011 7:48 AM
>To: pgsql-general@postgresql.org
>Cc: 'Brent Wood'
>Subject: Re: [GENERAL] Read MS-SQL data into Postgres via ODBC link?

>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.

 

Susan

 

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

From
Alban Hertroys
Date:
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
theirserver, 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
notdo 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.
Whenyou 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
every1000 rows or whatever. 

For those queries you will need a similarly prepared Postgres equivalent of the database schema of course, and quite
possiblysome 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
aneasier 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:737,4e139a9a12091547611509!



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

From
"Jonathan Brinkman"
Date:
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!




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

From
Ivan Sergio Borgonovo
Date:
On Tue, 5 Jul 2011 19:38:25 -0400
"Jonathan Brinkman" <JB@BlackSkyTech.com> wrote:

> 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.

I wrote some scripts, mainly in php and perl that make use of
freetds/odbc to migrate a schema from MSSQL to pg and import the data
via csv.
Postgresql can run perl, python and php "internally" and all those
languages have odbc drivers so you could skip the csv passage.
It's not elegant as it would be accessing the data directly from
odbc in pg but it works.
I can share some code.

> 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.

Nice. I'd be interested in some feedback if you succede to make it
work.
Does this project has any chance to be included in contrib? It seems
alive and kicking.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it