2 Replies Latest reply: Jul 24, 2013 7:03 PM by benton RSS

    Insert records from a single table to two related tables

    benton

      DB - 10G
      OS - XP

       

      We have many thousands of comma delimited records that we want to insert into a normalised table structure.

       

      I have created a test dataset that can be found at the end of this post.

       


      I have csv records that look like this;

       

      donald, huey
      donald, dewey
      donald, louie

       

      And I want the data to be inserted into two separate table like this;

       

      table named PARENTS
      pk parent_name
      1  donald

       

      Table named CHILDRENS
      pk fk child_name
      1  1 huey
      1  2 dewey
      1  3 louie

       


      I constructed an insert statement that looks like this;

       

      INSERT ALL
        INTO parents (parent_id, parent_name) VALUES (parents_seq.nextval, parent_name)
        INTO children (children_id, parent_id, child_name) VALUES(children_seq.nextval, parents_seq.nextval, child_name )
      SELECT parent_name, child_name
        FROM sources;

       

      And this resulted in the following;

       

      Table named PARENTS
      pk child_name
      1  DONALD
      2  DONALD
      3  DONALD

       

      Table named PARENTS
      pk fk child_name
      1  1  HUEY
      2  2  DEWEY
      3  3  LOUIE

       

      Would anyone have any ideas on how I could accomplish this task?

       

       

      This is some example data;

      [code]
      DROP SEQUENCE parents_seq;
      DROP SEQUENCE sources_seq;
      DROP SEQUENCE children_seq;
      DROP TABLE sources;
      DROP TABLE children;
      DROP TABLE parents;

       

      CREATE SEQUENCE PARENTS_SEQ MINVALUE 1 MAXVALUE 1000000000000000000000000000 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

       

      CREATE TABLE PARENTS
      ( PARENT_ID      NUMBER(8) NOT NULL
      , PARENT_NAME    VARCHAR2 (50 CHAR) NOT NULL
      , CONSTRAINT     PARENTS_PK PRIMARY KEY (PARENT_ID)
      );

       

      create or replace
      TRIGGER PARENTS_BI
      BEFORE
        INSERT OR UPDATE ON PARENTS
        FOR EACH ROW BEGIN

       

        IF INSERTING THEN

       

          IF :NEW.PARENT_ID IS NULL THEN
            SELECT PARENTS_SEQ.nextval INTO :NEW.PARENT_ID FROM dual;
          END IF;

       

        END IF;

       

      END;
      /

       


      CREATE SEQUENCE SOURCES_SEQ MINVALUE 1 MAXVALUE 1000000000000000000000000000 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

       

      CREATE TABLE SOURCES
      ( SOURCE_ID      NUMBER(8) NOT NULL
      , PARENT_NAME    VARCHAR2 (50 CHAR) NOT NULL
      , CHILD_NAME    VARCHAR2 (50 CHAR) NOT NULL
      , CONSTRAINT     SOURCES_PK PRIMARY KEY (SOURCE_ID)
      );

       

      create or replace
      TRIGGER SOURCES_BI
      BEFORE
        INSERT OR UPDATE ON SOURCES
        FOR EACH ROW BEGIN

       

        IF INSERTING THEN

       

          IF :NEW.SOURCE_ID IS NULL THEN
            SELECT SOURCES_SEQ.nextval INTO :NEW.SOURCE_ID FROM dual;
          END IF;

       

        END IF;

       

      END;
      /

       

      INSERT INTO SOURCES (PARENT_NAME, CHILD_NAME) VALUES ('DONALD', 'HUEY');
      INSERT INTO SOURCES (PARENT_NAME, CHILD_NAME) VALUES ('DONALD', 'DEWEY');
      INSERT INTO SOURCES (PARENT_NAME, CHILD_NAME) VALUES ('DONALD', 'LOUIE');
      Commit;

       


      CREATE SEQUENCE CHILDREN_SEQ MINVALUE 1 MAXVALUE 1000000000000000000000000000 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

       

      PROMPT *** CREATE TABLE ***
      CREATE TABLE CHILDREN
      ( CHILDREN_ID    NUMBER NOT NULL
      , PARENT_ID      NUMBER NOT NULL
      , CHILD_NAME     VARCHAR2 (50 CHAR) NOT NULL
      , CONSTRAINT     CHILDREN_PK PRIMARY KEY (CHILDREN_ID)
      );

       

      create or replace
      TRIGGER CHILDREN_BI
      BEFORE
        INSERT OR UPDATE ON CHILDREN
        FOR EACH ROW BEGIN

       

        IF INSERTING THEN

       

          IF :NEW.CHILDREN_ID IS NULL THEN
            SELECT CHILDREN_SEQ.nextval INTO :NEW.CHILDREN_ID FROM dual;
          END IF;

       

        END IF;

       

      END;
      /

       

      [code]

        • 1. Re: Insert records from a single table to two related tables
          Purvesh K

          Looks like this is one way of achieving it:

           

          insert into parents (parent_name) select distinct parent_name from sources;
          
          select *
            from parents;
          PARENT_ID              PARENT_NAME                                        
          ---------------------- -------------------------------------------------- 
          1                      DONALD
            
          insert into children (parent_id, child_name) 
          select p.parent_id, s.child_name
            from sources s
            join parents p
              on (s.parent_name = p.parent_name);
              
          select *
            from children;
          
          CHILDREN_ID            PARENT_ID              CHILD_NAME                                         
          ---------------------- ---------------------- -------------------------------------------------- 
          1                      1                      HUEY                                               
          2                      1                      DEWEY                                              
          3                      1                      LOUIE
          
          • 2. Re: Insert records from a single table to two related tables
            benton

            Thank You for your assistance.

            Ben