Inserting rows for multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inserting rows for multiple tables

Author

Topic

eplam2
Starting Member

11 Posts

Posted - 2013-09-03 : 04:47:43

Say for instance I got 2 tables
Subject Table and a Student Table
The Subject Table consist of the following attributes:
Subject_ID [PK], Subject_Name, Course_ID and Course_Name
The Student Table consists of the following attributes:
Subject ID [FK], Students_Name, Students_bday, Students_age, Students_height and Students_weight

How can I use the INSERT function when I would like to add a row with the following details:
Course_Name : Biotechnology
Students_Name : Fred
Students_bday : 01/JAN/1990
Stundets_age : 54

Im confused as to how to use the INSERT function for multiple tables.

Could any help me out? Thanks in Advance! :)

Kind Regards,
Elvin

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-03 : 05:23:09

what should be the Subect_name, course_id for course_name "Biotechnology"?

if Subject_id is IDENTITY column
DECLARE @SubjectId INT
INSERT Subject (Subject_Name, Course_ID and Course_Name) VALUES( 'XXXX', 1, 'Biotechnology')
SET @subjectId = SCOPE_IDENTITY();
INSERT Student (Subject ID, Students_Name, Students_bday, Students_age)
VALUES(@subjectId , 'Fred', '01/JAN/1990', 54)

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-03 : 05:27:05

I think you are using Oracle...
There is no SCOPE_IDENTITY() in Oracle...
Refer this link
http://stackoverflow.com/questions/3468482/how-to-get-the-id-of-the-added-row-in-oracle

Better post oracle questions in the relevant forum such as dbforums.com...
sqlteam.com is for SQL Server forum

--
Chandu

eplam2
Starting Member

11 Posts

Posted - 2013-09-03 : 08:48:16

There is no given Subject Name or Course ID, so that's assume as a null value

What does the function 'SCOPE_IDENTITY()' do?

Which function in Oracle replaces it?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-03 : 08:52:52

First clarify me some doubts
1) If subject name is NULL then why you should you insert into Subject Table?
2) You passed CourseName. Where do you insert that value? For what purpose you are passing CourseName?
SCOPE_IDENTITY() will return the auto incremented value of SubjectID in the case of above script..
whereas Oracle has sequences to have auto increment numbers...

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources