Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    3

    Question Unanswered: how to insert data in two tables to another table

    can any one plz tell me how to insert data in two table to another table.i have two main tables and a relation table between them.i need to insert the data of main tables into relation table.i have given the data below:
    create table employee
    (
    eid number (10) primary key,
    ename varchar2(20)
    );
    create table qualification
    (
    qcode number(10) primary key,
    qname varchar2(20),
    qyear number(4)
    );
    i have used sequence and trigger for auto increment and insertion of qcode

    CREATE SEQUENCE qualification_sequence
    START WITH 1
    INCREMENT BY 1;

    CREATE OR REPLACE TRIGGER qualification_trigger
    BEFORE INSERT
    ON qualification
    REFERENCING NEW AS NEW
    FOR EACH ROW
    BEGIN
    SELECT qualification_sequence.nextval INTO :NEW.QCODE FROM dual;
    END;
    /

    create table has
    (
    eid number(10) references employee,
    qcode number(10) references qualification
    );

    now i need to insert eid of employee table and qcode of qualification into has table.can any one plz help me in inserting them into has.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >now i need to insert eid of employee table and qcode of qualification into has table.can any one plz help me in inserting them into has.
    "design" is flawed!
    no common column exist between employee & qualification tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    @anacedent: The table HAS seems to be designed for storing this M:N relationship.

    Quote Originally Posted by ESHA View Post
    now i need to insert eid of employee table and qcode of qualification into has table.can any one plz help me in inserting them into has.
    Bunch of INSERT statements depending on real relationship between rows in EMPLOYEE and QUALIFICATION tables.
    If you just want to INSERT there random rows, use CROSS JOIN and eventually filter some rows e.g. using DBMS_RANDOM.VALUE function.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >@anacedent: The table HAS seems to be designed for storing this M:N relationship.
    Yes, but one can not use employee table & qualification table to load HAS table.
    The relationship between employee & qualification must originate from some data source; other than the two "parent" tables.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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