Thread: MS SQL 7.0 to PostgreSQL 7.1
Hi, I am looking at PostgreSQL as an alternative to Microsoft SQL. I have a database I would like to move to PostgreSQL to test how long it will take to move files, how to best change our software to access Postgre, etc.. I have PostgreSQL setup on a Test machine running Redhat 7.1. I have Postgre successfully installed and I also have phpPgadmin installed and configured. I have created a database, and a table, just trying to get use to Postgre. My question is what is the best way to import my data from MS SQL. I installed the ODBC Drivers and tried exporting using the MS import/export tool.. It successfully creates the tables, but fails to import any data, with a error stating that the relationship doesn't exist. So there goes the easy route and MS POS tool.. What's the correct way of doing this?? Thanks in advance Ryan C. Bonham
--- "Ryan C. Bonham" <Ryan@srfarms.com> wrote:
> My question is what is the best way to import my data from MS
> SQL. I
> installed the ODBC Drivers and tried exporting using the MS
> import/export
> tool.. It successfully creates the tables, but fails to import
> any data,
> with a error stating that the relationship doesn't exist. So
> there goes the
> easy route and MS POS tool.. What's the correct way of doing
> this?? Thanks
> in advance
First, verify that all of the data types of the old SQL Server
tables were correctly converted to Postgres. Then to transfer
the rest of the data over use the MS bulk copy (bcp) tool or the
export tool (MSSQL 7 or higher I believe) to create
tab-delimited dump files for each table. Then you must add
this to the beginning of each dump file: { COPY "table_name"
FROM stdin; } And add a { \. } at the end of the dump file.
Then you can use { psql -f table_name.dump } to import the data
from the dump files.
Brent
__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/
Also:
* Watch out for embedded tabs and carriage returns in your data: these
will cause problems during or after your COPY into PostgreSQL.
* Check the value used in the exported file to represent NULL values
(could be an empty string, or "NULL", or something else), and use that in
your COPY statement: "COPY table from stdin with null as 'whatever';"
> -----Original Message-----
> From: Brent R. Matzelle [SMTP:bmatzelle@yahoo.com]
> Sent: Wednesday, May 09, 2001 2:40 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] MS SQL 7.0 to PostgreSQL 7.1
>
> --- "Ryan C. Bonham" <Ryan@srfarms.com> wrote:
> > My question is what is the best way to import my data from MS
> > SQL. I
> > installed the ODBC Drivers and tried exporting using the MS
> > import/export
> > tool.. It successfully creates the tables, but fails to import
> > any data,
> > with a error stating that the relationship doesn't exist. So
> > there goes the
> > easy route and MS POS tool.. What's the correct way of doing
> > this?? Thanks
> > in advance
>
> First, verify that all of the data types of the old SQL Server
> tables were correctly converted to Postgres. Then to transfer
> the rest of the data over use the MS bulk copy (bcp) tool or the
> export tool (MSSQL 7 or higher I believe) to create
> tab-delimited dump files for each table. Then you must add
> this to the beginning of each dump file: { COPY "table_name"
> FROM stdin; } And add a { \. } at the end of the dump file.
> Then you can use { psql -f table_name.dump } to import the data
> from the dump files.
>
> Brent
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Auctions - buy the things you want at great prices
> http://auctions.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
In article <08CD1781F85AD4118E0800A0C9B8580B094A74@NEZU>, "Jeff Eckermann" <jeckermann@verio.net> wrote: > Also: > * Watch out for embedded tabs and carriage returns in your data: these > will cause problems during or after your COPY into PostgreSQL. * Check > the value used in the exported file to represent NULL values (could be > an empty string, or "NULL", or something else), and use that in your > COPY statement: "COPY table from stdin with null as 'whatever';" My experience is that MSSQL7 will export (bcp) NULLs as an ASCII zero character. Not pretty. You'll want to put together a little PERL script to fix that. Gordon. -- It doesn't get any easier, you just go faster. -- Greg LeMond
Hi all,
If someone wants to create a script and article for doing conversions
from MS SQL Server to PostgreSQL, this would be Really Beneficial. If
it needs to be hosted somewhere, then it can be put on
techdocs.postgresql.org (or elsewhere as the author wishes, with a link
to it on techdocs).
:-)
Regards and best wishes,
Justin Clift
Gordon Runkle wrote:
>
> In article <08CD1781F85AD4118E0800A0C9B8580B094A74@NEZU>, "Jeff Eckermann"
> <jeckermann@verio.net> wrote:
>
> > Also:
> > * Watch out for embedded tabs and carriage returns in your data: these
> > will cause problems during or after your COPY into PostgreSQL. * Check
> > the value used in the exported file to represent NULL values (could be
> > an empty string, or "NULL", or something else), and use that in your
> > COPY statement: "COPY table from stdin with null as 'whatever';"
>
> My experience is that MSSQL7 will export (bcp) NULLs
> as an ASCII zero character. Not pretty. You'll want
> to put together a little PERL script to fix that.
>
> Gordon.
> --
> It doesn't get any easier, you just go faster.
> -- Greg LeMond
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
Does anyone have a script to deal with embedded carriage returns? If not no
biggy i can create one i suppose... Just trying to save myself some time..
-----Original Message-----
From: Justin Clift [mailto:justin@postgresql.org]
Sent: Thursday, May 10, 2001 8:57 AM
To: Gordon Runkle
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] RE: MS SQL 7.0 to PostgreSQL 7.1
Hi all,
If someone wants to create a script and article for doing conversions
from MS SQL Server to PostgreSQL, this would be Really Beneficial. If
it needs to be hosted somewhere, then it can be put on
techdocs.postgresql.org (or elsewhere as the author wishes, with a link
to it on techdocs).
:-)
Regards and best wishes,
Justin Clift
Gordon Runkle wrote:
>
> In article <08CD1781F85AD4118E0800A0C9B8580B094A74@NEZU>, "Jeff Eckermann"
> <jeckermann@verio.net> wrote:
>
> > Also:
> > * Watch out for embedded tabs and carriage returns in your data:
these
> > will cause problems during or after your COPY into PostgreSQL. *
Check
> > the value used in the exported file to represent NULL values (could be
> > an empty string, or "NULL", or something else), and use that in your
> > COPY statement: "COPY table from stdin with null as 'whatever';"
>
> My experience is that MSSQL7 will export (bcp) NULLs
> as an ASCII zero character. Not pretty. You'll want
> to put together a little PERL script to fix that.
>
> Gordon.
> --
> It doesn't get any easier, you just go faster.
> -- Greg LeMond
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)