SQL 如何将值插入具有外键关系的两个表中？

How to insert values into two tables with a foreign key relationship?

提问by Ca Pham Van

I created two tables:

我创建了两个表：

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

• Table tblRoleOfStaffwith columns id(primary key, auto increment), StaffId(foreign key to tblStaff), RoleId

• 表tblStaff的列id（主键，自动递增）name，age，address

• 表tblRoleOfStaff的列id（主键，自动递增）， StaffId（外键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 tblStaffand insert new record into tbleRoleOfStaffwith staffIdI just inserted.

我想写在SQL Server 2014存储过程中插入新员工tblStaff，并插入新记录tbleRoleOfStaff与staffId我刚插入。

What should I do?

我该怎么办？

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

如果我的问题与其他问题重复，我很抱歉。我在 SQL 方面比较新鲜。谢谢你的帮助。

采纳答案by gofr1

Use SCOPE_IDENTITY()second insert into tblRoleOfStuffon a place of StaffId. Like:

使用SCOPE_IDENTITY（）第二插入到tblRoleOfStuff上占有一席之地的StaffId。喜欢：

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().

如果 OP 保证他不会使用他可能使用的任何触发器@@IDENTITY（不好的做法），这足以满足他的需要，但最好的做法是使用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() 与@@IDENTITY 一样，将返回在当前会话中创建的最后一个标识值，但它也会将其限制在您当前的范围内。换句话说，它将返回您明确创建的最后一个标识值，而不是由触发器或用户定义的函数创建的任何标识。

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

SCOPE_IDENTITY()将保证您从当前操作中获得身份，而不是从另一个连接或上次处理的连接中获得身份。

Why not IDENT_CURRENT? Because

为什么不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.

IDENT_CURRENT 不受范围和会话的限制；它仅限于指定的表。IDENT_CURRENT 返回为任何会话和任何范围内的特定表生成的标识值。

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)

所以你取最后一个范围但不是当前范围。是的，OP 也可以使用它，但在那种情况下这是一种不好的做法（比如只使用@@IDENTITY）

Using OUTPUTis 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.

使用OUTPUT确实是一种很好的做法，但对于只有一个身份来说过于复杂。如果 OP 需要一次处理多于一行 - 是的，他需要 OUTPUT.

回答by Matt

Because it seems like you are discussing 1 row at a time some people may tell you to use a system vairable like @@IDENTITYor some of the others but to ensure with more certainty I recommend the OUTPUTclause 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.

因为看起来您一次讨论 1 行，所以有些人可能会告诉您使用系统变量@@IDENTITY或其他一些系统变量，但为了确保更确定，我建议OUTPUT使用插入语句的子句。这种方法的好处是它可以很容易地适应一次处理超过 1 行。

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 @@IDENTITYin 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_IDENTITYhas a similar shortfall when a trigger modifies the same table. IDENT_CURRENThas a short coming too. Do an internet search to learn more there are tons of great resources on these.

@@IDENTITY在执行与您的操作相关联的其他操作时不使用的原因。例如，触发器将另一行插入到另一个表中，或更新数据库中的另一条记录。SCOPE_IDENTITY当触发器修改同一个表时，也有类似的不足。 IDENT_CURRENT也有一个短暂的未来。进行互联网搜索以了解更多信息，这些方面有大量优秀资源。

回答by Alex Kudryashev

You can use outputfrom your first insert statement.

您可以output从第一个插入语句中使用。

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

回答by Ashwin Kambli

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

回答by Vahid Farahmandian

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_IDENTITYand @@IDENTITY. Read about it Here

请注意IDENT_CURRENT,SCOPE_IDENTITY和之间的区别@@IDENTITY。在这里阅读

回答by Sam

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)