p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Language (http://p2p.wrox.com/forumdisplay.php?f=100)
-   -   insert value into multiple table (http://p2p.wrox.com/showthread.php?t=52364)

AzlanAziz January 7th, 2007 10:45 AM

insert value into multiple table
 
i hav 2 tables where the field StudentID is in both table.StudentID is a primary key in table Student and becomes foreign key in table StudentSchedules(StuddentID isn't auto increment). How can i populate both table at the same time?

CREATE TABLE [Student] (
    [StudentID] VARCHAR(40) NOT NULL,
    [Name] VARCHAR(40),
    CONSTRAINT [PK_Student] PRIMARY KEY ([StudentID])
)

CREATE TABLE [StudentSchedules] (
    [ClassID] INTEGER IDENTITY(0,1) NOT NULL,
    [ClassStatus] VARCHAR(40),
    [StudentID] VARCHAR(40) NOT NULL,
    CONSTRAINT [PK_StudentSchedules] PRIMARY KEY ([ClassID], [StudentID])
)

ALTER TABLE [StudentSchedules] ADD CONSTRAINT [Student_StudentSchedules]
    FOREIGN KEY ([StudentID]) REFERENCES [Student] ([StudentID]) ON DELETE CASCADE ON UPDATE CASCADE

code_lover January 7th, 2007 05:17 PM

Now as far as i can see you have a parent child relationship between the two tables.The Student table is the master table and the StudentSchedules table is the detail table(1:M relationship).
We do not usually come up with a scenario in this case where we have to insert data into 2 tables simultaneously.Now will you just specify what really you want to insert into tables.Can you just explain in more detail what are you trying to achieve?
If you just want to populate the 2 tables , you really need to execute 2 separate INSERT statements in SQL to do that to the best of my knowledge.



AzlanAziz January 7th, 2007 08:55 PM

erm i'm a newbie & dunno if the design is faulty, but i wanted to crete two section in my asp page. One for student details and another for the classSchedules. so i wanted to make sure that if the student entered his particular in textbox the value could be placed in the foreign key in the classSchedules table to enforce the relationship... So that he could associated 2-3 classes depending on the classSchedules section in the asp.net page. Any suggestion on how to do this? Thx 4 the comments

code_lover January 8th, 2007 01:48 PM

Okay this makes the picture a bit clear now.You have to have a third table here i.e. Classes(ClassID,Class)(Total Tables:3).You are suppose to enter basic student details just once in the Students section(i.e. student name, id)in the Students table right.
And for the StudentSchedules section you have to have 2 combo boxes populated with StudentID and a ClassID in your ASP page.Whenever a user clicks or selects the StudentID and ClassID, the names of the student and class should appear.
Remember the more tables you create and the more narrower your tables are going to be does make your application's performance better.
I hope this helps and you get the idea. Keep on going ...you're on the right path, wish you best of luck!


AzlanAziz January 9th, 2007 08:56 AM

Well about the combo boxes... i actually thought of different scenario
(i change the primary key of the student table to be autoincremented).
well the issue now is what if i create a new table which is empty? (eg: the user can freely insert any clubname in the textbox.... the student can be associated with 2-3 clubs)
is the procedure below the right way to it?... sorry to trouble u much but i'm kinda stuck right now..

INSERT INTO student
                      (studentname)
VALUES ('cassidy')
INSERT INTO Club(studentid, Clubname)
VALUES (SCOPE_IDENTITY(), 'history')

__________________________________________________ _____


CREATE TABLE [student] (
    [studentid] INTEGER IDENTITY(0,1) NOT NULL,
    [studentname] VARCHAR(40),
    CONSTRAINT [PK_student] PRIMARY KEY ([studentid])
)

CREATE TABLE [Club] (
    [studentid] INTEGER,
    [Clubnameid] INTEGER IDENTITY(0,1) NOT NULL,
    [Clubname] VARCHAR(40),
    CONSTRAINT [PK_classname] PRIMARY KEY ([classid])
)
GO



code_lover January 12th, 2007 06:01 AM

ummm...well as far as i can see you are on the right track.In the Club table you will have to have a composite primary key(combination of two columns i.e. StudentID and ClubID).
Just carry on what you are trying to do, so far i cannot see any deficiency in the table design.If any problem arises then do let me know.


MAXOOD!

Life is an endless journey towards perfection


All times are GMT -4. The time now is 01:34 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.