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?