问题描述
我创建了两个表:
-
表
tblStaff
和列id
(主键,自动递增),名称
,age
,地址
Table
tblStaff
with columnsid
(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()
将确保您从当前操作获得身份,而不是从另一个连接或最后一个处理的身份获得.
SCOPE_IDENTITY()
will guarantee that you get identity from current operation, not from another connection or last one processed.
为什么不是
IDENT_CURRENT
?因为
因此,您需要接受最后一个作用域而不是当前作用域.是的,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
.
这篇关于如何将值插入具有外键关系的两个表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!