how to insert data in subtype table and get key from super type in T-SQL?

Question

For example got in super type a user:

create table Userr
(
    id int primary key identity,
    firstName nvarchar(20),
    lastName nvarchar(20),
    email nvarchar(15) unique not null,
    userName nvarchar(15) unique not null,
    passKey nvarchar(15) not null,
    sex nvarchar(1),
    userType nvarchar(1),
    depID nvarchar(5) ,
)

Then in sub-types I got student and instructor :

create table Student
(
    stuID int primary key,
    gradYear date ,
    constraint c2 foreign key(stuID) references Userr(id)
)

create table Instructor
(
    insID int primary key,
    salary money ,
    constraint c3 foreign key(insID) references Userr(id)
)

All I want is to add new student and new instructor, how can I do this in an efficient way?

Answer 1

all I want is to add new student and new instructor ,how can I make it in efficient way?

The meaning of "subtype table" is not clear, but in order to INSERT new student and new instructor you should use simple INSERT query.

INSERT Userr(firstName,lastName,email, userName, passKey)
    VALUES ('Ronen','Ariely', 'NotForYou','pituach','NoWay')
GO
INSERT Student(stuID, gradYear) VALUES (1, '2001-02-27')
GO

you had to have the referenced user in the userr table first. This mean that you had to first INSERT the referenced user if it does not exists already.