none
How to insert values in two relational tables?

    Question

  • I have created four tables which all are related to each other as follows:

    Table 1: Project

    PrjID (Primary key) and ProjectTitle

    Table 2: Student

    StdID(Primary key) PrjID(Foreign Key) and StdName

    Table 3: Course

    CrsId(Primary key) and CourseTitle

    Table 4: Teacher

    TchID(Primary Key), CrsID(Foriegn Key), StdID(Foreign Key), YearSemester, NoOfSection, LabHours, and NoOfStudents.

    What will be the SQL query to insert values in these all tables at a time or individually without any kind of conflict? waiting reply its too much important please

    Sunday, January 12, 2014 8:51 PM

Answers

  • Your tables seem to be incorrect (the Student and Teacher tables). First of all, I think you may want to use plural for the tables' names, e.g.

    Projects

    Students

    Courses

    Teachers

    ----------------------

    Now, we also need to have a table that will link Students and Projects and another table that will link Teacher and Courses the teacher is going to teach.

    And we also need to have a table of StudentCourses (the courses the student takes).

    So, I would start from re-designing your schema.

    I have an article that will help you, but the site right now is undergoing some changes, so I will provide the link later (remind me, please).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, January 12, 2014 8:58 PM
  • Assuming that all "Primary Key" fields in each table are of integer datatype & incremental fields. You can try something like this,

    INSERT INTO dbo.PROJECT(ProjectTitle) VALUES ('PROJECT2')
    INSERT INTO dbo.STUDENTS(PrjID,StdName) VALUES (IDENT_CURRENT('dbo.PROJECT'),'JACK')
    To know more about the recent identity value refer this link http://technet.microsoft.com/en-us/library/ms175098.aspx

    Regards, RSingh

    Monday, January 13, 2014 5:49 AM

All replies

  • Your tables seem to be incorrect (the Student and Teacher tables). First of all, I think you may want to use plural for the tables' names, e.g.

    Projects

    Students

    Courses

    Teachers

    ----------------------

    Now, we also need to have a table that will link Students and Projects and another table that will link Teacher and Courses the teacher is going to teach.

    And we also need to have a table of StudentCourses (the courses the student takes).

    So, I would start from re-designing your schema.

    I have an article that will help you, but the site right now is undergoing some changes, so I will provide the link later (remind me, please).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, January 12, 2014 8:58 PM
  • Assuming that all "Primary Key" fields in each table are of integer datatype & incremental fields. You can try something like this,

    INSERT INTO dbo.PROJECT(ProjectTitle) VALUES ('PROJECT2')
    INSERT INTO dbo.STUDENTS(PrjID,StdName) VALUES (IDENT_CURRENT('dbo.PROJECT'),'JACK')
    To know more about the recent identity value refer this link http://technet.microsoft.com/en-us/library/ms175098.aspx

    Regards, RSingh

    Monday, January 13, 2014 5:49 AM