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

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Norman Khine <norman(at)khine(dot)net>
Cc: Pgsql-Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Multiple table insert using a CSV list as the data
Date: 2002-07-14 20:34:46
Message-ID: 1026678887.10875.67.camel@kant.mcmillan.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Norman Khine 2002-07-14 21:10:36 Re: Multiple table insert using a CSV list as the datasource
Previous Message Norman Khine 2002-07-14 16:25:21 Re: Multiple table insert using a CSV list as the data source