Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
265 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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Screencast - Getting to Know the Pipeline

886 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