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.

Answer 2

Because it seems like you are discussing 1 row at a time some people may tell you to use a system vairable like @@IDENTITY or some of the others but to ensure with more certainty I recommend the OUTPUT clause of the insert statement. The good thing about this method is it can easily be adapted to handle more than 1 row at a time.

DECLARE @Output AS TABLE (StaffId INT)

INSERT INTO tblStaff (name, age, address)
OUTPUT inserted.Id INTO @Output (StaffId)
VALUES (@name, @age, @address)

DECLARE @StaffId INT
SELECT @StaffId = StaffId FROM @Output

INSERT INTO tblRoleOfStaff (StaffId, RoleId)
VALUES (@StaffId,@RoleId)

Reasons not to use @@IDENTITY in case another operation linked to yours is performed. E.g. a trigger inserts another row into another table, or updates another record in your database., SCOPE_IDENTITY has a similar shortfall when a trigger modifies the same table. IDENT_CURRENT has a short coming too. Do an internet search to learn more there are tons of great resources on these.