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

Question

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 1

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)

EDIT

There too much comments on this answer, so I want to give an explanation.

If OP guarantee that he will not use any triggers he may use @@IDENTITY (bad practice), it is sufficient enough to his needs, but best practice to use SCOPE_IDENTITY().

SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SCOPE_IDENTITY() will guarantee that you get identity from current operation, not from another connection or last one processed.

Why not IDENT_CURRENT? Because

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

So you make take last scoped but not current one. Yes, OP can use it too, but it is a bad practice in that situation (like using only @@IDENTITY)

Using OUTPUT is indeed good practice, but over complicated for only one identity. If OP need to process more then one row in a time - yes, he need OUTPUT.