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

Hi Andrew,
Thanks for the advice, much appreciated.

I did put the Access table on the Posgre db, it is the csv table, of my
original post.

Can you advise on how to write an sql statement which will convert this one
table into my INSERT statement?

Is there such an SQL statement where you say:

Take table csv and put column 1, 2, 3 into table products where column 1
should map to products.column1 etc etc ...
or take table csv and put column1 and column 4 into table price

Can you provide me with an example, if possible?

Regards

Norman


-----Original Message-----
From: Andrew McMillan [mailto:andrew@catalyst.net.nz]
Sent: 14 July 2002 21:35
To: Norman Khine
Cc: Pgsql-Novice
Subject: Re: [NOVICE] Multiple table insert using a CSV list as the
datasource


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?