Re: Multiple table insert using a CSV list as the data

On Mon, 2002-07-15 at 00:29, Norman Khine wrote:
> Hello,
> I have a postgre database and I have a list of about 12,000 entries. My
> problem in short is that I am trying to transfer an Access database to
> Postgres and I am trying to match a very broken Access db schema an input
> the data onto an sort of OK postgre schema - if this makes any sense;^)
>
> Anyway, I am able to extract the data from the Access db into a sort of
> managable file, but I am having difficulties in writing the sql insert
> statement so that ALL parts of the dataabse are filled in at the same time.

What I do in this sort of case is to import the data straightforwardly
from the Access tables into temporary PostgreSQL tables, and then I
write a file of SQL commands which convert those tables into my final
tables. This doesn't have to be a single "INSERT ..." statement, and
indeed it usually isn't. Sometimes I might enclose it in a transaction
block if I want it to happen atomically.

I run the script of SQL commands using "psql -f filename.sql
<database>".

Once I am happy with the way my conversion script works, and the
PostgreSQL database is 'live', I drop the temporary tables with the old
Access data.

Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?