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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
c# combobox autocomplete behavior 6 92
find a node in VST 2 47
What is Python programming? 3 68
Hide vba in gp 7 48
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now