Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 7th, 2007, 10:45 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to AzlanAziz
Default 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
  #2 (permalink)  
Old January 7th, 2007, 05:17 PM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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.


  #3 (permalink)  
Old January 7th, 2007, 08:55 PM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to AzlanAziz
Default

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
  #4 (permalink)  
Old January 8th, 2007, 01:48 PM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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!

  #5 (permalink)  
Old January 9th, 2007, 08:56 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to AzlanAziz
Default

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


  #6 (permalink)  
Old January 12th, 2007, 06:01 AM
Authorized User
 
Join Date: Jan 2007
Location: , , .
Posts: 46
Thanks: 2
Thanked 1 Time in 1 Post
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
INSERT multiple rows to a table nvillare Oracle 7 November 3rd, 2007 04:47 AM
insert multiple records into a table from values Deepak Chauhan Oracle 3 May 12th, 2006 10:35 PM
How do I insert data into multiple table ? bmalex1 PHP How-To 0 February 2nd, 2006 02:43 PM
Insert multiple records in a Inner Join Table rylemer Access 1 August 22nd, 2004 07:44 AM
Insert multiple records in a Inner Join Table rylemer Access VBA 0 July 19th, 2004 03:54 PM





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