Re: Bulk Insert / Update / Delete OR...

Or you could write your data to text file:

100,1,'+23456789'
101,1,'+23456767'

with a table:

CREATE TABLE import (
accnum int4,
instance int4,
phone varchar(15)
) without oids;

then in a script:

cat datafile.txt | psql -ddatabase -c "COPY import FROM STDIN DELIMITER ',';"
echo "
BEGIN;

UPDATE T_SUBSET SET "Telephone_Number" = import.phone FROM
import WHERE "Account_Number" = accnum AND "S_Occurance" = instance;

INSERT INTO T_SUBSET SELECT accnum,instance,phone FROM
(SELECT accnum,instance,phone FROM import WHERE NOT EXISTS
(SELECT * FROM T_SUBSET WHERE "Account Number" = accnum AND
"S_Occurance" = instance));

COMMIT;" | psql -ddatabase

Or something like that.. and only use 3 sql statements for the whole lot.
That way you don't even have to form your queries in your text file and just
pipe raw data through.

Rgds,

Jason

On Fri, 22 Aug 2003 07:13 am, Philip Boonzaaier wrote:
> Hi Ron
>
> I could pursue the pre-compiler route, but I'm actually trying to avoid
> this. The more independant I can make the RDBMS, the better. Step 1 will be
> to put the data in a decently aranged set of tables, Step 2, generate the
> business rules and logic as functions in the database, Step 3, re-write the
> front ends as now 'logic free' data capture windows.
>
> We are using Client / Server tools with the COBOL. So, while the Clients
> are Windows based, the Server is UNIX or LINUX. What we have working so
> far, is the gerneration of the appropriate SQL commands, into a text file,
> with the time incorporated in the name. No more than 60 seconds later, we
> have a UNIX script, running in the background, which picks up the file, and
> executes the batch.
>
> T_SUBSET would be defined as having a FOREIGN KEY linking it to T_MAIN, but
> other that the Key columns, the data in T_SUBSET is unique, and does not
> exist in T_MAIN.
>
> For example, I would CREATE T_MAIN like this :
>
> CREATE TABLE T_MAIN
> (Account_Number INTEGER NOT NULL,
> Account_Name VARCHAR (40),
> PRIMARY KEY (Account_Number));
>
> And T_SUBSET would be :
>
> CREATE TABLE T_SUBSET
> (Account_Number INTEGER NOT NULL,
> S_Occurance INTEGER NOT NULL,
> Telephone_Number VARCHAR(15),
> PRIMARY KEY (Account_Number,S_Occurance),
> FOREIGN KEY(Account_Number)
> REFERENCES T_MAIN(Account_Number));