This discussion is archived
2 Replies Latest reply: Jul 24, 2013 5:03 PM by benton RSS

Insert records from a single table to two related tables

benton Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    Thank You for your assistance.

    Ben

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points