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.

Solution 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.

Solution 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.

Solution 3

You can use output from your first insert statement.

declare @tmp table(id int)
insert tblStaff (name, age, address)
OUTPUT inserted.Id INTO @tmp (id)
values (@name, @age, @address)

declare @roleId int = 1 --or whatever
insert tblRoleOfStaff (staffId,roleId)
select id, @roleId
from @tmp

You can insert several roles at once as well.

create table Roles (roleId int identity(1,1) primary key, 
RoleName varchar(50),
isDefaultRole bit default 0
)
--mark some roles as default (`isDefaultRole = 1`)
--the 2nd insert will be
insert tblRoleOfStaff (staffId,roleId)
select id, roleId
from @tmp
cross join Roles
where isDefaultRole = 1

Comments

[Matt]

most of the time @@identity will probably get what you want but do a search for @@Identity, SCOPE_IDENTITY etc. it is very easy to write a trigger or a multi statement stored procedure that will mess up which identity you are really trying to get.

[gofr1]

OP question is about simple form with 4 fields. @@IDENTITY is all he need in that case. If he has something with insert of big amount of data - yes, @@IDENTITY will not be enough.

[marc_s]

I would recommend to use SCOPE_IDENTITY() instead of anything else (like @@IDENTITY) to grab the newly inserted identity value. See this blog post for an explanation as to WHY

[marc_s]

This will NOT work in a busy system! You CANNOT rely on the "fact" that the last inserted row has the highest Id - other inserts may have happened before you get a chance to read that value!

[Sam]

Agreed. But given the table structure and the fact that this a fresher trying out SQL I wasn't worrying about a busy system. My bad.

[gofr1]

@marc_S thanks for explanation, I changed my answer to SCOPE_IDENTITY()

[Vahid Farahmandian]

@gofr1 Scope_Identity() is not a good solution for a big and busy system. using IDENT_CURRENT is better that using Scope_Identity().

[Matt]

@marc_s I am sticking with OUTPUT clause even Microsoft says potential of incorrect values with SCOPE_IDENTITY() plus note this answer originally said @@IDENTITY. This question is definitely a duplicate! support.microsoft.com/en-us/kb/2019779 I understand in OPs particular example either SCOPE_IDENTITY() or @@IDENTITY should be sufficient but things change someone else writes a trigger he isn't aware of etc. I have literally never seen someone write a trigger that will alter the IDENTITY column and change the output inserted.id value though I suppose even that is possible

[gofr1]

@VahidFarahmandian yes, it is not good for a busy system (I already wrote that in a comment above), but here is a simple form with 4 fields and SCOPE_IDENTITY is sufficient enough for this purpose.

[marc_s]

Pinal Dave actually recommends to use SCOPE_IDENTITY in that article you linked to: To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or stored procedure.

[gofr1]

@Matt this problem was fixed in 2008 R2, and OP use SQL Server 2014, so he can use SCOPE_IDENTITY()/@@IDENTITY without any problems. I add information about triggers in my answer as well. Thanks for noticing!

[Sj03rs]

@Sam, although it's quite obvious that this will not work on a busy system, also coincidence might also appear, making the system "break" for the user. Might aswell explain it the right way, right?