MS SQL -> PostgreSQL: COPY command

[Terence Kearns]

Any particular reason why it *has* to be mysql?
(ie, it must run on windows (and you don't want to use cygwin)...)

if not, postgres is "extremely fast" at accepting data via it's COPY 
command.
what you can do is dump the MSSQL table to a file in tab deliminated (or 
any other deliminated) format and then use COPY to import it. See 
http://techdocs.postgresql.org/techdocs/usingcopy.php

If you do have the ability to use PG instead of mysql, you will find 
that PG is much more ANSI compliant then mysql and this is important for 
conversions fro mssql server which is also more ansi compliant than 
mysql. You will also be able to port reference constraints (foreign 
keys), triggers, procedures, rules, and checks form MSSQL (with some 
effort). With mysql, you won't even have the option. Mysql also has a 
repuation for being a lot faster than PG but that information is out of 
date. the new 7.2.x series has cranked up the speed particularly if you 
actually make a tiny effort to tune the thing!

BTW, I just bought this book yesterday
*PHP and PostgreSQL Advanced Web Programming*
http://www.amazon.com/exec/obidos/ASIN/0672323826/qid=1034733519/sr=2-2/ref=sr_2_2/002-7042832-8172838

and I've already read the first 8 chapters. Can I just say "This book 
seriously kicks butt!". It's a real eye-opener to potgres's hardcore 
features. The book claims to have run an experiment (using PHP) where 
using multiple insert statements took 13minutes compared to the COPY 
command which took 4 seconds. In this case, the copy command was run 
within PHP and it took input from stdin (as opposed to a file).

To be perfectly honest, I can't for the life of me understand this 
obsession with mysql when you have postgres... anyway, it's not 
productive to start a debate on that again.

Chris Fletcher wrote:

>Slightly off the main topic here but ...
>
>Can anyone recommend a 'safe' way to import the entire contents of an MS
>SQL7 database table into a MYSQL database.  The MS SQL database contains
>about 10 million records and cant really be taken offline so it needs to be
>done live.  And without impacting performance either.
>
>Oh, and they would like fries with that too!  Sheesh!
>
>Any thoughts or suggestions would be appreciated.
>
>I was thinking of linking to the mysql table from the mssql server and
>running a select/insert query to populate it.  I suspect though that this
>will kill the ms sql server for a while which would be bad.
>
>Is there a better way??
>
>Chris
>