将tbl1的主键插入tbl2的外键，将tbl1、tbl2的主键插入tbl3的外键

Question

 CREATE TABLE tbl1 as [departments]
 (
   deptId int identity primary key,
   deptName nvarchar(40)
 )

 CREATE TABLE tbl2 as [teachers]
 (
   teacherId int identity primary key,
   teacherName nvarchar(40),
   deptId int references departments
 )

CREATE TABLE tbl3 as [departmentChiefs]
(
  deptId int references departments,
  teacherId int references teachers,
  chiefDate date
)

我想将(部门)表的主键插入到(教师)表的外键中，也将(部门和教师)的主键插入到departmentChiefs的外键中

Answer 1

试试这个：

DECLARE @DeptId INT, @TeacherId INT;

INSERT INTO departments (deptName) VALUES ('My Department');
SELECT @DeptId = SCOPE_IDENTITY();

INSERT INTO teachers (teacherName, deptId) VALUES ('Joe Smith', @DeptId);
SELECT @TeacherId = SCOPE_IDENTITY();

INSERT INTO departmentChiefs (deptId, teacherId, chiefDate) VALUES (@DeptId, @TeacherId, GETDATE());

SCOPE_IDENTITY返回插入到当前作用域中的最新标识值，结果可以用作另一个INSERT语句的输入。您可能还想在使用它之前做一些错误检查(例如，确保它不是NULL )。