Ca Pham Van Ca Pham Van - 5 months ago 22
SQL Question

How to insert values into two tables with a foreign key relationship?

I created two tables:


  • Table
    tblStaff
    with columns
    id
    (primary key, auto increment),
    name
    ,
    age
    ,
    address

  • Table
    tblRoleOfStaff
    with columns
    id
    (primary key, auto increment),
    StaffId
    (foreign key to
    tblStaff
    ),
    RoleId



I have form to create new staff with existing role. Data sample to insert:

(name, age, address, roleId) = ('my name',20,'San Jose', 1)


I want to write a stored procedure in SQL Server 2014 to insert new staff to
tblStaff
and insert new record into
tbleRoleOfStaff
with
staffId
I just inserted.

What should I do?

I am so sorry if my question is duplicate with other. I am fresher in SQL.
Thanks for any help.

Answer

Use SCOPE_IDENTITY() second insert into tblRoleOfStuff on a place of StaffId. Like:

insert into tblStaff values
(@name, @age, @address)

insert into tblRoleOfStuff values
(scope_identity(), @roleid)
Comments