Dear All,

I need some help on procedures. I have a table which has around 5000 records. I need to insert additional 200 records, i have insert statements ready for the same. I need to make sure while inserting that if records does not exists in the table it should insert them or else update them, there is a unique constraint on the id. If records exists it throws me a unique constraint violation error. How can i do it with the help of procedure. Please advice.

Best Regards,

Lloyd

What about creating a temp table with the same structure, loading the records to the temp table, then doing an insert for those that don't exist in the original?

CREATE TEMP TABLE tmptbl (
field1 char(1)
) WITH NO LOG;

INSERT INTO tmptbl <--- 200 records


INSERT INTO origtbl
SELECT * FROM tmptable WHERE id NOT IN (SELECT id FROM origtbl)

I'm sure there's better ways to do it, but this is the way I've always done it...

<edit post>

Forgot to address your updates... you could either do this before or after the inserts... if you do this before, it would have less work to do since it wouldn't be running against the records you just inserted...

UPDATE origtbl
SET (field1, field2, ..., fieldn) = (SELECT field1, field2, ..., fieldn FROM tmptbl
WHERE tmptbl.id = origtbl.id)
WHERE id IN (SELECT id FROM tmptbl)

slow & clunky but works, from what I remember...

Hi Kueheri,

Thanks for your feedback, i'll work on it.

Regards,

Lloyd