如何将值插入具有外键关系的两个表中? [英] How to insert values into two tables with a foreign key relationship?

问题描述

我创建了两个表:

• 表 tblStaff 和列 id (主键，自动递增)，名称， age ，地址

• Table tblStaff with columns id (primary key, auto increment), name, age, address

表 tblRoleOfStaff ，其中列 id (主键，自动递增)， StaffId ( tblStaff <的外键)， RoleId

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)

我想在SQL Server 2014中编写一个存储过程，以将新人员插入到 tblStaff 中，并使用 staffId 将新记录插入到 tbleRoleOfStaff 中刚刚插入.

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.

我该怎么办?

很抱歉，我的问题与其他人重复.我在SQL方面比较新鲜.感谢您的帮助.

I am so sorry if my question is duplicate with other. I am fresher in SQL. Thanks for any help.

推荐答案

使用 SCOPE_IDENTITY()第二次插入到 StaffId 位置的 tblRoleOfStuff 中.喜欢:

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)

编辑

对此答案的评论过多，所以我想给出一个解释.

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

如果OP保证他不会使用任何触发器，则可以使用 @@ IDENTITY (不好的做法)，这足以满足他的需要，但是最好的做法是使用 SCOPE_IDENTITY().

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()与@@ 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()将确保您从当前操作获得身份，而不是从另一个连接或最后一个处理的身份获得.

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

为什么不是 IDENT_CURRENT ?因为

IDENT_CURRENT不受范围和会话的限制；它仅限于指定的表.IDENT_CURRENT返回在任何会话和任何作用域中为特定表生成的标识值.

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.

因此，您需要接受最后一个作用域而不是当前作用域.是的，OP也可以使用它，但是在那种情况下(例如仅使用 @@ IDENTITY )，这是一个不好的做法

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)

使用 OUTPUT 确实是一种好习惯，但是对于一个身份而言却过于复杂.如果OP需要一次处理多个行-是的，他需要 OUTPUT .

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.

这篇关于如何将值插入具有外键关系的两个表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！