locked
Data entry into multiple tables using a stored procedure RRS feed

  • Question

  • Hi

    I am planning on creating an asp.net interface for this sql schema design below to allow data entry.  I am planning on using stored procedures to do this.  My question is, how can I do this?  How will the stored procedure work to update the tables at once?  I want one screen for data entry which will allow the student to enter their details, enter their course, and coursework details.   How can I write a stored proceudre to update all table at once and reflect the primary and foreign keys? How do the other tables update with these?  e.g. if a student enters in their details, how does the coursework details table know what data to put into the PK, FK columns?

    I was planning on using maybe the username and email address as primary keys.  How do I pass these parameters though to the other linked tables using the sp?

    CREATE TABLE Student
    (  Student_user_name nvarchar(50) CONSTRAINT PKStudent  PRIMARY KEY,
    Student_Email_address nvarchar(max),
    Student_forename nvarchar(50),
    Student_surname nvarchar(50),
    Datecreated timestamp
    )

    CREATE TABLE Course
    (
    Course_name nvarchar(100) CONSTRAINT PKCourse PRIMARY KEY
    )

    CREATE TABLE Course_Work
    (  student_user_name nvarchar(50) NOT NULL,
       course_name nvarchar(100) NOT NULL,
      CONSTRAINT PKCourse_Work PRIMARY KEY (student_user_name, course_name),
      courseworkmark int,
    Datecreated timestamp
    )

    ALTER TABLE Course_work ADD CONSTRAINT FKCourse_work$references$course 
                  FOREIGN KEY (course_name) REFERENCES course(Course_name)
    ALTER TABLE Course_work ADD CONSTRAINT FKCourse_work$references$student
                 FOREIGN KEY (student_user_name ) REFERENCES student(student_user_name )

    Thanks

    Tuesday, April 10, 2012 2:08 PM

Answers

  • Consider not using the email or name as PK in Student.  Also, don't use Course_name as the PK in Course.  Rather, go with an identity (or sequence, if you have SQL 2012).

    You appear to need a stored proc that uses a transaction.  I'll asume that the Course table is populated by the system admin and not by the student.  The transaction would essentiall go as:

    begin tran

    insert into Student ...

    select @ID = scope_identity()

    insert into Course_Work ...

    commit tran

    The @ID would be the FK that you use in the Course_Work table to reference Student.

    As for the paramters, since a student can enrol in mutiple course, cosider using a table variable as the proc parameter for the input for the Course_work table.


    Tom
    SQL Server MVP
    Toronto, ON Canada

    • Proposed as answer by Tom Moreau Tuesday, April 10, 2012 2:16 PM
    • Marked as answer by Kalman Toth Thursday, April 19, 2012 6:36 PM
    Tuesday, April 10, 2012 2:16 PM

All replies

  • Consider not using the email or name as PK in Student.  Also, don't use Course_name as the PK in Course.  Rather, go with an identity (or sequence, if you have SQL 2012).

    You appear to need a stored proc that uses a transaction.  I'll asume that the Course table is populated by the system admin and not by the student.  The transaction would essentiall go as:

    begin tran

    insert into Student ...

    select @ID = scope_identity()

    insert into Course_Work ...

    commit tran

    The @ID would be the FK that you use in the Course_Work table to reference Student.

    As for the paramters, since a student can enrol in mutiple course, cosider using a table variable as the proc parameter for the input for the Course_work table.


    Tom
    SQL Server MVP
    Toronto, ON Canada

    • Proposed as answer by Tom Moreau Tuesday, April 10, 2012 2:16 PM
    • Marked as answer by Kalman Toth Thursday, April 19, 2012 6:36 PM
    Tuesday, April 10, 2012 2:16 PM
  • Create Procedure InsertCW(@susername nvarchar(50), @semail nvarchar(50), @sforename nvarchar(50) , @ssurname nvarchar(50), @cname nvarchar(100), @cwmark int) 
    as
    Begin
      Insert into Student (Student_user_name,  Student_Email_address, Student_forename, Student_surname) values (@susername, @semail, @sforename, @ssurname)
      Insert into Course(Course_name) Values (@cname)
      Insert into Course_Work(student_user_name, course_name, courseworkmark) Values (@susername, @cname, @cwmark)
    End
    go

    Exec InsertCW 'Nazer', 'Nazer@email.com', 'Nazer', 'Mohamed', 'MVP', 100
    Tuesday, April 10, 2012 2:31 PM
  • Best way is you keep StudentId as primary key.

    If you want to check that no email-id is duplicate then you can check for emailId before insert record.

    You can also add unique constraint on email_id.

    Any specific need for creating Email_id or username as primary key?

    If you use integer as Primary key, you can make use of

    1. scope_identity()
    2. @@Identity

    Whichever is appropriate in your case.

    • Proposed as answer by Virat Singh Tuesday, April 10, 2012 4:46 PM
    Tuesday, April 10, 2012 4:46 PM
  • Hi,

    You can have all your suggestion above. but make sure that all covers in one transaction.

    Tuesday, April 10, 2012 4:56 PM
  • Hi

    Thanks for your replies.  I like the thought of using a transaction and scope_identity() for the table design I have supplied above, how would this be done?  I would like an example of a transaction with scope_identity to work from so I can base the my design on it.  Will the same scope identity be produced in the different tables so I could use this to link the transactions in a SQL query.

    I am basically looking for the easiest way to link the tables together so I can see how many courseworks one student has completed etc.

    Thanks

    Tuesday, April 10, 2012 5:00 PM
  • I have a sample of using transactions, scope_identity (although a better solution is to use OUTPUT clause of INSERT command) and error handling.

    Please see this sample

    How to insert information into multiple related tables and return ID using SQLDataSource

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


    My blog

    Tuesday, April 10, 2012 5:03 PM
  • Thanks Naomi, i would like to start using the best solution so that I do not have issues further down the line.  Do you have an example of output clause of insert or error handling? 
    Tuesday, April 10, 2012 5:13 PM
  • The OUTPUT clause is really simple and documented best in BOL. 

    declare @Output table (fields here should repeat your actual table)

    INSERT INTO myTable (field1, field2, ...)

    OUTPUT Inserted.* INTO @Output

    values (@Val1, @Val2)

    INSERT INTO RelatedTable (FirstTableFK, ...)

    select O.FirstTablePK, @Val3, ...

    FROM @Output O

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

    In other words, using OUTPUT clause you can get identity field or all default values fields (everything which was just inserted gets outputted into this table). Now you can use that table (@Output) in other statements that involve using a field's value from related table.

    The solution I pointed you to uses very similar idea by using scope_identity() instead.


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


    My blog

    Tuesday, April 10, 2012 5:24 PM
  • Hi Nazer, How can I add a unique identifier like scope_identity() to this script?  What do you suggest for a unique identifier when inserting into multiple tables?  Thanks :-)
    Wednesday, April 11, 2012 8:07 PM
  • Hi Naomi I am not sure if I understand the output clause.  Basically does this mean that everything is put into one table, then filtered out to other ones with the same ID?  Thanks
    Wednesday, April 11, 2012 8:08 PM
  • Hi Nazer, How can I add a unique identifier like scope_identity() to this script?  What do you suggest for a unique identifier when inserting into multiple tables?  Thanks :-)

    A unique identifier is a property of a column in your table.  If you a numericl column with an identity property, then you can use scope_identity with that column.  Here's an example:

    create table t
    (
       ID   int identity
            primary key
    ,  junk varchar (10)
    )
    go
    
    insert t (junk) values ('abc')
    
    select scope_identity()
    go
    


    Tom
    SQL Server MVP
    Toronto, ON Canada

    Wednesday, April 11, 2012 8:11 PM
  • Hi Tom, thank you for your post.  I have my table layout sorted, I just need to decide on a primary key.  Its actually alot more difficult than I first imagined.    I am going to be creating an asp user interface which will allow the user to log in and I want their account to be linked e.g. course, coursework and student details. Do you think scopeidentity is best to use? 
    Wednesday, April 11, 2012 8:24 PM
  • Hi Tom, thank you for your post.  I have my table layout sorted, I just need to decide on a primary key.  Its actually alot more difficult than I first imagined.    I am going to be creating an asp user interface which will allow the user to log in and I want their account to be linked e.g. course, coursework and student details. Do you think scopeidentity is best to use? 

    Scope_identity() is useful only if you are using an identity, which I do recommend you use.  The beauty about it is that it automatically generates an ID.  The user does not have to see this ID.  If your PK is clustered on it, then fragmentation of the data pages is minimized.  Also, what if the user changes his email?  If you had the PK on email, then you'd have to cascade the changes to the dependent tables.  If you had an identiy as the PK, then if the user changed his email, it would be altered in only one place, with no need to cascade.  Finally, a 4-byte integer is a lot smaller than, say, a 255-byte varchar.

    Tom
    SQL Server MVP
    Toronto, ON Canada

    Wednesday, April 11, 2012 8:30 PM
  • INSERT, UPDATE, DELETE, MERGE commands in SQL Server have OUTPUT clause. By using it, we can get all newly inserted rows into a table. So, by using Inserted.* we get all values for every column even if some columns were omitted in the INSERT (such as identity column or columns with default values).


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


    My blog

    Wednesday, April 11, 2012 8:31 PM
  • Hi Tom

    I have created my table with the identity for the student_id.  I have amended the stored procedure with @@Identity.  When I go to execute the stored procedure I get an error:

    Msg 1088, Level 16, State 11, Procedure insertdata, Line 6
    Cannot find the object "student1" because it does not exist or you do not have permissions.

    Can you advise why?  Thanks for your help :-)

    TABLE:

    CREATE TABLE Student

    (
    Student_Id int identity(1,1),
      Student_user_name nvarchar(50),
    Student_Email_address nvarchar(max),
    Student_forename nvarchar(50),
    Student_surname nvarchar(50),
    Datecreated timestamp
    )

    CREATE TABLE Course
    (
    Course_name nvarchar(100)
    )

    CREATE TABLE Course_Work
    ( Student_Id  int identity(1,1),
             student_user_name nvarchar(50) ,
       course_name nvarchar(100),
      courseworkmark int,
    Datecreated timestamp
    )

    STORED PROCEDURE:

    Create Procedure insertdata
    (@student_id int, @susername nvarchar(50), @semail nvarchar(50), @sforename nvarchar(50) , @ssurname nvarchar(50), @cname nvarchar(100), @COURSEWORKMARK INT)

    as
    Begin
    SET IDENTITY_INSERT student1 ON
    Insert into Student (Student_id, Student_user_name,  Student_Email_address, Student_forename, Student_surname) values (@student_id, @susername, @semail, @sforename, @ssurname)

    Insert into Course(Course_name) Values (@cname)

    Insert into Course_Work(Student_id, student_user_name, course_name, courseworkmark) Values (@student_id, @susername, @cname, @COURSEWORKMARK)

    select @student_id=@@identity
    RETURN
    End
    go

    EXECUTE STORED PROCEDURE:

    use student1;
    go
    Exec insertdata @student_id=@@identity, @susername='joebloggs', @semail='joebloggs@email.com', @sforename='jo', @ssurname='bloggs', @cname='Computing', @COURSEWORKMARK=100

    Wednesday, April 11, 2012 10:47 PM
  • This line SET IDENTITY_INSERT student1 ON references Student1 instead of Student. Also, why do you need to pass @studentId? Also, the last line in your sp select @student_id = @@identity makes no sense as you're not using this as output parameter. So, comment this line.

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


    My blog

    Thursday, April 12, 2012 1:57 AM
  • Hi Naomi, I need to pass the student Id so that it appears in both tables so I can link them e.g. if a student inserts their details (data goes into the studetns table) and if they insert their coursework details this will go into the coursework table.  I need their id in both tables so I can link them.  Is this not ok?  Will comment out that line :-)

    Thursday, April 12, 2012 9:16 AM
  • You don't want to set identity_insert on.  That has the effect of overriding the identity.  The identity autogenerates the ID.  In pseudocode, we have:

    insert Student (Name, Email) values ('John Smith', 'john@nomail.com');
    
    set @ID = scope_identity();  -- picks up the ID
    
    insert CourseWork (StudentID, CourseID) values (@ID, 123);


    Tom
    SQL Server MVP
    Toronto, ON Canada

    Thursday, April 12, 2012 11:49 AM
  • In this case you don't want to override the identity key or pass the @StudentID in. You want to pass it back as an OUTPUT parameter. The blog post I already gave reference to explains exactly the same problem of inserting information into related tables using SCOPE_IDENTITY() function to get id. It also uses transactions and error handling.

    In my other reply to you I showed, that it's better to use OUTPUT clause of the INSERT command than SCOPE_IDENTITY() although most likely there is no difference for 99.999% scenarios.


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


    My blog

    Thursday, April 12, 2012 12:41 PM