CODEWITHSUNDEEP
sqloracle-databaserdbms
Dilan Perera
Dilan Perera

Reputation: 23

Oracle adding records from multiple tables

My objective is to build a master Serial No table from many different tables and each Serial No must be unique.

 CREATE TABLE  "TBL_SERIAL_NUMBER_MASTER" 
   (    "INTERNAL_RECORD_ID" VARCHAR2(60) NOT NULL ENABLE, 
    "ASSET_ID" VARCHAR2(60), 
    "SERIAL_NUMBER" VARCHAR2(1000) NOT NULL ENABLE, 
    "VALID" VARCHAR2(60) DEFAULT 'Valid', 
    "HOST_NAME" VARCHAR2(255), 
     CHECK ( valid IN ('Invalid', 'Valid')) ENABLE, 
     CHECK ( valid IN ('Invalid', 'Valid')) ENABLE, 
     CHECK ( valid IN ('Invalid', 'Valid')) ENABLE, 
     CONSTRAINT "TBL_SERIAL_NUMBER_MASTER_CON" PRIMARY KEY ("SERIAL_NUMBER") ENABLE
   ) ;


CREATE OR REPLACE TRIGGER  "TBL_SERIAL_NUMBER_MASTER_INTER" BEFORE
  INSERT
    ON tbl_serial_number_master FOR EACH ROW  WHEN (
      NEW.internal_record_id IS NULL
    ) BEGIN :NEW.internal_record_id := tbl_serial_number_master_inter.NEXTVAL;
END;
/
ALTER TRIGGER  "TBL_SERIAL_NUMBER_MASTER_INTER" ENABLE;

I have already updated the SERIAL_NUMBER field using one table. Now I am trying to add more Serial Nos from another table. These other tables have duplicate and null value Serial Nos. Can you please advise how I can create the query to add unique Serial Nos that are already not in the TBL_SERIAL_NUMBER_MASTER.

Please assume following fields for the other table.

TABLE_SOURCE_B

  1. ID :-PK
  2. SERIAL_NUMBER
  3. List item

Name

Upvotes: 0

Views: 74

Answers (1)

Harshil Doshi
Harshil Doshi

Reputation: 3592

In your Master Table, 'Internal_Record_Id' has Not Null constraint. So I am inserting values of ID field of Source table into 'internal_record_id'. Try the following query.

 INSERT INTO 
    TBL_SERIAL_NUMBER_MASTER (INTERNAL_RECORD_ID,SERIAL_NUMBER) 
 SELECT  ID,SERIAL_NUMBER 
    FROM TABLE_SOURCE_B B 
       WHERE B.SERIAL_NUMBER NOT IN(SELECT SERIAL_NUMBER 
                                              FROM TBL_SERIAL_NUMBER_MASTER);

Hope it helps!

Upvotes: 0

Related Questions