Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    262

    Unanswered: Help on Stored Procedures

    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

  2. #2
    Join Date
    Jul 2004
    Posts
    8
    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...



    Quote Originally Posted by lloydnwo
    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
    Last edited by kueheri; 07-21-04 at 12:08. Reason: additional info

  3. #3
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Hi Kueheri,

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

    Regards,

    Lloyd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •