Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: Comparing Two Tables - Need Advice

    I need advice on a SQL problem. Here is the background info:

    I have two Oracle 9i tables. The column definitions in both tables are almost the same. One table has more defined attributes that the other. Each table are defined in separate schemas. User ID permission levels allows access to both schemas. The primary key definitions are the same in both tables.

    I am setting up an Oracle trigger in which when the primary table (let's call it Table_A) encounters new rows to be inserted a trigger condition is invoked that will execute a stored procedure. This stored procedure compares all the rows from the primary table (Table_A) to the alternate table (let's call it Table_B) matching on the primary key fields in both tables.

    When the primary key value from Table_A is not found in the alternate table (Table_B) I want to setup code that will insert the contents from Table_A to Table_B.

    To illustrate the problem here is some example code for your review -

    insert Table_B
    (
    reservation_pk,
    name,
    address,
    reservation_date
    )
    select
    p.reservation_pk,
    p.name,
    p.address,
    " "

    from dbo.Table_A p

    where not exist
    reservation_pk = p.reservation_pk

    Can anyone provide me with a good SQL example?

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    CREATE OR REPLACE TRIGGER sysadm.triggernam
    AFTER INSERT ON tablename
    REFERENCING NEW AS NEWDATA OLD AS OLDDATA FOR EACH ROW
    DECLARE
    V_NUMBER NUMBER;

    BEGIN
    SELECT COUNT(*) INTO V_NUMBER
    FROM TABLE_B
    WHERE KEYVALUE = :NEWDATA.KEYVALUE;

    IF V_NUMBER < 1
    THEN
    INSERT INTO TABLE_B (COL1, COL2)
    VALUES (:NEWDATA.COL1, :NEWDATA.COL2)
    END IF;
    END;
    /

    HTH
    Gregg

Posting Permissions

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