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
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Make the most of your online learning experience.
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…
Starting up a Project

623 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