Solved

How can i create a foreign key value in the child table for every primary key value created?

Posted on 2004-08-17
4
229 Views
Last Modified: 2010-04-17
If I have an insert stored procedure like say;

CREATE PROC procTest

@Title varchar(4)= [NOT NULL],
@FirstName varchar(15)= [NOT NULL],
@LastName varchar(25)= [NOT NULL],
@Branch varchar(28)= [NOT NULL]
AS
INSERT INTO tblTest(Title,FirstName,LastName,Branch)
VALUES(@Title,@FirstName,@LastName,@Branch)

How can I modify this code so that it retrieves the new identity value and writes this into another table as the foreign key

In other words when sql server creates a new primary key value how can I create the appropriate foreign key in the child table?

Much appreciated!



0
Comment
Question by:pgilfeather
4 Comments
 
LVL 2

Accepted Solution

by:
sajid_bwp earned 500 total points
ID: 11821113
Hello Dear,
if you want to get the ID of Latest inserted/deleted/modified record in SQL Server you can use the [ @@Identity ]
This will return you the ID of last inserted/deleted/modified record.
For example your have table Employee
[it is supposed that the ID is autogenrated]

Insert stored procedure

//Start of Stored Procedure
Create Proc InsertEmployee
@FirstName as varchar,
@LastName as varchar
insert into Employee(First_Name,Last_Name) Values(@FirstName,@LastName)

// @@Identity //This statement will return you the Empoloyee_ID for example for the last Record, it is 10

declare variable @Emp_ID as int
set @emp_ID=@@Identity  //Save it in variable, and enjoy new inserations on the basis of this ID

//here you can place the code for new inseration
//end of stored Procedure


Employee_ID    First_Name  Last_Name
    10                    Sajid        Majeed



Good Luck
Sajid Majeed
0
 
LVL 7

Expert Comment

by:vnvk
ID: 11822201
Sajid's code is right for your scenario. Infact, its the most widely followed method.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about a problem I had getting the GPS LocationListener working.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question