Geek Answers Handbook

How to insert values ​​into two tables with foreign key relation?

I created two tables:

  • tblStaff table with id columns (primary key, automatic growth), name , age , address

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

I have a form for creating new employees with an existing role. Sample data 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 in tblStaff and insert a new record in tbleRoleOfStaff using the StaffId that I just inserted.

What should I do?

I am very sorry if my question is duplicated with another. I am better at SQL. Thanks for any help.

+5
source share
5 answers

Use SCOPE_IDENTITY () to insert in tblRoleOfStuff in place of StaffId . How:

 insert into tblStaff values (@name, @age, @address) insert into tblRoleOfStuff values (scope_identity(), @roleid)

EDIT

Too many comments on this subject, so I want to give an explanation.

If the OP guarantees that he will not use triggers, he can use @@IDENTITY (bad practice), this is enough for his needs, but it is best to use SCOPE_IDENTITY() .

SCOPE_IDENTITY (), for example @@ IDENTITY, will return the last authentication value created in the current session, but it will also limit it to your current scope. In other words, it will return the last identifier value that you explicitly created, and not any identity created by a trigger or user-defined function.

SCOPE_IDENTITY() ensures that you get the identifier from the current operation, and not from another connection or the last processed one.

Why not IDENT_CURRENT ? Because

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

Thus, you are doing the last busy, but not the current one. Yes, the OP can also use it, but this is bad practice in this situation (for example, using only @@IDENTITY )

Using OUTPUT really good practice, but more difficult for just one identity. If the OP needs to process more than one line at a time - yes, it needs OUTPUT .

+8
source

Since it seems that you are discussing 1 line at a time, some people may tell you to use a system similar to @@IDENTITY or some others, but for more certainty I recommend the OUTPUT clause of the insert expression. The good thing about this method is that it can be easily adapted to process more than 1 line 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 if you are performing another operation related to yours. For instance. a trigger inserts another row into another table or updates another record in your database. SCOPE_IDENTITY has a similar flaw when a trigger modifies the same table. IDENT_CURRENT also has a short circuit. Do an online search to find out more, there are many great resources on them.

+2
source

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 also insert multiple roles at once.

 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
+2
source

After pasting 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)
-1
source

Try the following:

 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

Note the differences between IDENT_CURRENT , SCOPE_IDENTITY and @@IDENTITY . Read about it here.

-1
source

More articles:


All Articles