Thread: Multiple COPYs

Hello,

Having a great time with PG - ported an erp from oracle and db2. First
I tried MySql but choked somewhere in the 900 table region. I have a
python script to translate the syntax and it loads about 2000 tables.

Now I want to COPY my dumps - I have 1 data dump for each table. Any
tips on what to use so that I can read the file name into a variable
and pass it as the file name in the COPY command and have one script
load all my tables?

Thanks for any help.

Rasta DJ

On Jun 16, 2005, at 12:32 PM, rasdj@frontiernet.net wrote:

> Hello,
>
> Having a great time with PG - ported an erp from oracle and db2. First
> I tried MySql but choked somewhere in the 900 table region. I have a
> python script to translate the syntax and it loads about 2000 tables.
>
> Now I want to COPY my dumps - I have 1 data dump for each table. Any
> tips on what to use so that I can read the file name into a variable
> and pass it as the file name in the COPY command and have one script
> load all my tables?

Why not use Python or a simple shell script to generate a file like:

\copy table1 from 'table1.txt'
\copy table2 from 'table2.txt'
....

And then do psql -f filename

Sean

rasdj@frontiernet.net writes:

> Hello,
>
> Having a great time with PG - ported an erp from oracle and db2. First
> I tried MySql but choked somewhere in the 900 table region. I have a
> python script to translate the syntax and it loads about 2000 tables.
>
> Now I want to COPY my dumps - I have 1 data dump for each table. Any
> tips on what to use so that I can read the file name into a variable
> and pass it as the file name in the COPY command and have one script
> load all my tables?

Should be pretty easy from the shell, something like (untested):

for f in *.sql; do
  tablename=`basename $f .sql`
  psql -c "\\copy $tablename from $f" mydatabase
done

-Doug