sql server - inserting primary key of tbl1 into foreign key of tbl2 and primary key of tbl1, tbl2 into foreign key of tbl3 -

 create table tbl1 [departments]  (    deptid int identity primary key,    deptname nvarchar(40)  )   create table tbl2 [teachers]  (    teacherid int identity primary key,    teachername nvarchar(40),    deptid int references departments  )  create table tbl3 [departmentchiefs] (   deptid int references departments,   teacherid int references teachers,   chiefdate date ) 

i want insert primary key of (departments) table foreign key of (teachers) table inserting primary key of (departments , teachers) foreign key of departmentchiefs

try this:

declare @deptid int, @teacherid int;  insert departments (deptname) values ('my department'); select @deptid = scope_identity();  insert teachers (teachername, deptid) values ('joe smith', @deptid); select @teacherid = scope_identity();  insert departmentchiefs (deptid, teacherid, chiefdate) values (@deptid, @teacherid, getdate()); 

scope_identity returns recent identity value inserted in current scope, , result can used input insert statement. might want little error checking before using (make sure it's not null example).