Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2011
    Posts
    6

    how to make a multple insert

    Hi every body.
    I've a got a problem for what i couldn't get an anwser.I'll try to explain the problem, through an example:
    supposing that i have two tables where each one references the other:

    emp(num_emp,name,department,num_ser)
    service(num_serv,name,num_emp)

    how can i do to insert in the same time using a view, through a trigger or whatever, into the tables?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one of the FKs will have to be nullable, and you will need to use two INSERT statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    6
    The problem is that i use an application where i need to insert the tuples through a view that include the two tables

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one of the FKs will have to be nullable, and you will need to use an INSERT and then an UPDATE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2011
    Posts
    6
    Thank you for your answer r937
    But could you show me how i can do that, to be executed in once, as trigger for example.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you dump the table layouts please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2011
    Posts
    6
    -- TABLE NUMber 1
    create table etudiant(num_etu int primary key ,nom varchar(20),prenom varchar(20),num_cours int )
    -- Table Number 2
    create table cours(num_cours int primary key,lib varchar(20),num_etu int)

    alter table etudiant add constraint fk1 foreign key(num_cours) references cours(num_cours)
    alter table cours add constraint fk2 foreign key(num_etu) references etudiant(num_etu)
    --------Creation of the view
    create view v1 as
    select e.num_etu,nom,prenom,c.num_cours,lib from etudiant e join cours c on e.num_etu=c.num_etu

    ----Creation of the trigger

    create trigger tr1
    on v1 instead of insert
    as
    INSERT INTO etudiant SELECT num_etu,nom,prenom,num_cours FROM INSERTED;
    INSERT INTO cours SELECT num_cours,lib,num_etu FROM INSERTED;

    ---Insertion through the view which will enable the trigger

    insert into sd1 values(1,'smith','fred',1,'Info',2)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you've completely lost me

    why don't your primary keys say NOT NULL?

    why are all other columns nullable? (e.g. it doesn't make much sense for the database to allow adding a student with null prenom and null nom)

    why does the cours table have a link to a single student?

    why can the student only have one course?

    where do the values of the primary keys in both tables come from?

    are you ~sure~ you know what you're doing with this design?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2011
    Posts
    6
    Hi r937
    why don't your primary keys say NOT NULL?
    I use sql server, and with it we can make NOT NULL using the graphics.

    why are all other columns nullable? (e.g. it doesn't make much sense for the database to allow adding a student with null prenom and null nom)

    The are not nullable, notice at the and i inserted into the view sd1 (i meant v1)

    why does the cours table have a link to a single student?

    In the where we have 1-1 cardinality, means that cours have 1 or 0 student, and student have 0 or 1 cours .

    the information came from an application java.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't think i can help you, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Aug 2011
    Posts
    6
    Ok.
    Thanks any way

Posting Permissions

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