From: Joe Conway <mail@joeconway.com>
To: Casey Allen Shobe <cshobe@secureworks.net>
Cc: pgsql-php@postgresql.org
Subject: Re: Moving data from other databases
Date: Tue, 27 Aug 2002 22:27:10 -0700
Message-ID: <3D6C5F2E.4060700@joeconway.com> (raw)
References: <200208280107.43674.cshobe@secureworks.net>
Casey Allen Shobe wrote:
> I have a large amount of data in DB2 and Microsoft SQL databases that I would
> like to copy to new tables in Postgres, primarily for testing and
> proof-of-concept purposes.
>
> I have a Linux/PHP installation supporting all three, and was wondering if PHP
> would be a good medium for working this task.
I think that depends on how much data is "a large amount" and how much
patience you have ;-)
Seriously, I think you would be better off using MSSQL's BCP (bulk copy)
program to create a tab delimited file and then use PostgreSQL COPY to
import the data. The process would be something like:
- Use bcp to export data file
- Possibly use sed/awk/your-favorite-text-processing-program to adjust
the output (only *if* necessary) -- things to watch out for here are
delimiter characters (e.g. tabs) embedded in you data which need
escaping of some sort, and NULL values. I can't remember how much
control BCP gives you in representing nulls, but pgsql COPY allows it
to be specified.
- Create tables in pgsql; do not create indexes or foreign keys if
possible at this point.
- Import data using COPY
- Create indexes and refint constraints
- Vacuum analyze
This process should be orders of magnatude faster than querying the data
out, looping through row-by-row, building and executing insert
statements, etc. I suppose you could build the import file using PHP and
then use Postgres COPY, but it would still be more work and alot slower
than using vendor provided export utilities.
I'm not familiar with DB2, but I'd guess it comes with some sort of bulk
export utility also.
HTH,
Joe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: mail@joeconway.com, cshobe@secureworks.net
Subject: Re: Moving data from other databases
In-Reply-To: <3D6C5F2E.4060700@joeconway.com>
References: <200208280107.43674.cshobe@secureworks.net> <3D6C5F2E.4060700@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox