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
237 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

839 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