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.

Cassatt

SQL Server doesn't have "auto increment". Are you sure you know what database you are using? – Dronski

Thank Gordon Linoff, I am using SQL Server Management Studio and I set Id column with type is int and then setting identity manually with identity increment with value is 1. – Cassatt

This is typically referred to as an "identity" column in SQL Server; an "auto increment" column in MySQL; a "serial" column in Postgres. They are all the same thing, but each database has a different syntax. – Dronski

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.

Aboulia

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. – Hoelscher

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. – Aboulia

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 – Myrtie

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

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

@Myrtie 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 – Hoelscher

@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. – Aboulia

@Hoelscher 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! – Aboulia

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.

Hoelscher

Answer 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

Philine

Answer 4

If it is just one staff row that you would insert at a time, you could do the following:

begin try
 begin tran
  insert into tblStaff (name, age, address) values('my name',20,'San Jose');
  insert into tbleRoleOfStaff (StaffId, RoleId) values (SCOPE_IDENTITY(), 1); 
 commit
end try
begin catch
  IF @@trancount > 0 ROLLBACK;
end catch

Semitic

Answer 5

Try this:

Create Procedure Pro_XXX()
AS
BEGIN

INSERT INTO tblStaff (name, age, address, roleId) VALUES ('my name',20,'San Jose', 1);

INSERT INTO tbleRoleOfStaff VALUES (staffId, roleId) VALUES (IDENT_CURRENT('tblStaff'),0)

END

Please note to the differences between IDENT_CURRENT, SCOPE_IDENTITY and @@IDENTITY. Read about it Here

Riobard

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. – Myrtie

Answer 6

After the insert into the first table use something like

DECLARE @staffId INT
SET @staffId = (SELECT TOP 1 id from tblStaff order by id desc)

INSERT INTO tblRoleOfStaff (staffId,roleId) VALUES (@staffId, 2)

Ethyne

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! – Myrtie

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. – Ethyne

@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? – Tricorn