Click here to Skip to main content
15,940,246 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All,

I have a table T1 with column EMPID as primary key with identity specification.

I have another table T2 with DEPT ID as primary key with identity specification and EMPID as Foreign key.

I have data in T1 like empid=24.

Now I want to insert the data into T2 using stored procedures.

I want EMPID value 24 to be inserted automatically while i am inserting values to T2 using stored procedure.

Can anyone help me regarding this.

Thank you,
Posted
Updated 25-May-12 1:04am
v2

First of all, follow this link: Creating Stored Procedures[^]

The below procedure selects employee by his/her FisrtName and LastName. If employee does not exists, adding data into Employee table, then adding data into Employee_Detail table.
SQL
CREATE STORED PROCEDURE AddEmpIntoDept
    @LastName NVARCHAR(50), 
    @FirstName NVARCHAR(50),
    @DepId INT
AS 
BEGIN
    DECLARE @empid INT
    DECLARE @retval INT

    SET NOCOUNT ON;
    SELECT @empid = ISNULL(EmpId,0), FirstName, LastName
    FROM Employees
    WHERE FirstName = @FirstName AND LastName = @LastName;

    IF  @empid = 0 
    BEGIN
        INSERT INTO Employess (FirstNam, LastName)
            VALUES (@FirstName, @LastName)
        SET @empid = @@IDENTITY
    END

    INSERT INTO Employees_Detail (EmpId, DeptId)
        VALUES(@DepId, @empid)

    SET @retVal = @@IDENTITY
   
    RETURN @retval
END
GO
 
Share this answer
 
Comments
Wendelius 25-May-12 16:04pm    
Nice answer
Maciej Los 26-May-12 16:25pm    
Thank you, Mika ;)
After you insert into T1 use
@@identity
value to send new record into T2 table as foreign key
 
Share this answer
 
Comments
jaipal0908 25-May-12 7:12am    
Thanks Gupta,
Can u post stored procedure to insert into T2 so that i can understand.
T1-->empid(pk)
T2-->deptid(pk with identity spec),empid(fk).

Thank you,
abhijeetgupta1988 25-May-12 7:23am    
create proc spInsert
(
@empname varchar(30)
)
as
begin

--First Insert
insert into T1
select
@empname

--Second Insert Begin
Select @fkEmpId =@@IDENTITY

insert into T2
(
fkEmpId
)
end
abhijeetgupta1988 25-May-12 7:26am    
I'm assuming EmpId & Deptid are Identity and Identity Incremented
might be better using Scope_Identity in 2008 don't think it'd matter for this particular case though.

see here..

http://msdn.microsoft.com/en-us/library/ms190315.aspx[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900