On Tue, Apr 15, 2008 at 9:54 PM, Andreas <maps.on@[EMAIL PROTECTED]
> wrote:
> Hi,
> I've got to im****t data into a remote database.
> I get some stuff usually as excel-files that doesn't fit the
db-structure
> in respect of normalisation so I im****t it into Access, brush up the
data
> and push the columns from there in the right tables via ODBC.
>
> For bigger im****ts (~5000 lines in Excel that get spread over 4-6
tables in
> my db) I fetch the remote DB, do the im****t locally and transfer a dump
back
> to the remote site when noone uses the server.
> This is getting unwieldy as the db grows and the connection is not
really
> that fast.
>
> I can access the server by ssh so it might be way faster to run a
prepared
> SQL file that consists just of the new data against the DB on the
console.
>
> Could I build a SQL script that adds the new stuff and creates foreign
keys
> on the fly without the need to know the new IDs before?
>
> Lets suppose I got a couple of foreign-linked tables:
> company (company_id serial primary key, name, ...)
> person (person_id serial primary key, company_fk, name, ...)
> contact (contact_id serial primary key, person_fk, contact_date,
> contact_notes, ...)
>
> The SQL script needed to:
> 1) INSERT INTO company ...
> 2) look up the last created company_id
> 3) INSERT INTO person and use the company_id to set company_fk
> 4) look up the last created person_id
> 5) INSERT INTO contact and use the person_id to set person_fk
> Probaply create some other persons with their contacts.
> Then another company and so on.
>
> I do this right now with vba in Access but this is painfully slow via
> InterNet as it constantly has to ask the server for the last ids and the
> Net's latency spoils all the fun.
Why not avoid Access altogether. Load the data into postgres "as-is"
into a set of loader tables. Then, you can use SQL to do the data
munging and inserts, including the foreign key relation****ps. You can
use \copy in psql to load the data, so there is no need to have access
to the server.
Sean
--
Sent via pgsql-novice mailing list (pgsql-novice@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
|
