SQL Server: Table with identity field and Record insertion with full and partial fields

--Creating Table 

--primary key defined with identity field

Create table tabEmployee(

EmpID int identity(1000,1),

EmpName char(20) not null,

Department char(30),

Designation char(20),

Salary int default 9000

);

--Note: 

--Identity field will start to populate automatically from 1000,

--will be incremented by 1 for subsequent record insertions.

--Inserting all fields

insert into tabEmployee values

('Neha Yadav','Development','Analyst Trainee',25000)

insert into tabEmployee values

('Shalini Patel','Development','Analyst Trainee',25000)

insert into tabEmployee values

('Abinesh Thangaraj','Development','Analyst Trainee',25000)

--Output

select * from tabEmployee

1000   Neha Yadav          Development    Analyst Trainee   25000

1001   Shalini Patel       Development    Analyst Trainee   25000

1002   Abinesh Thangaraj   Development    Analyst Trainee   25000

--Partial insertion.

--This insertion requires a list of non-nullable fields, 

--they are getting inserted into the table

--Default value fields will have its default value 

--when it has not received value.

insert into tabEmployee(EmpName,Department,Designation) values

('Syed Nawas','Development','Technical Trainer')

insert into tabEmployee(EmpName,Department,Designation) values

('Sameeha Syed','Development','Technical Trainer')

--Output

select * from tabEmployee

1000   Neha Yadav          Development    Analyst Trainee     25000

1001   Shalini Patel       Development    Analyst Trainee     25000

1002   Abinesh Thangaraj   Development    Analyst Trainee     25000

1003   Syed Nawas          Development    Technical Trainer    9000

1004   Sameeha Syed        Development    Technical Trainer    9000

--Inserting NULL values to nullable fields

--In the above table, Department and Designations fields

--were not declared as not null columns. So, those columns

--will accept null values like below.

insert into tabEmployee(EmpName,Designation) 

values('Saleema Parvin','Technical Trainer')

--Department missed.

insert into tabEmployee(EmpName,Department) 

values('Asrafi Basha','Training')

--Designation missed.

--Output

select * from tabEmployee

1000   Neha Yadav          Development    Analyst Trainee     25000

1001   Shalini Patel       Development    Analyst Trainee     25000

1002   Abinesh Thangaraj   Development    Analyst Trainee     25000

1003   Syed Nawas          Development    Technical Trainer    9000

1004   Sameeha Syed        Development    Technical Trainer    9000
1005   Saleema Parvin      NULL           Technical Trainer    9000
1006   Asrafi Basha        Training       NULL                 9000

--Inserting multiple rows in a single SQL query

INSERT INTO tabEmployee

  (EmpName,Department,Designation)

VALUES

  ('Aadham AS','Training', 'Behavioral Trainer'),

  ('Ibrahim AS', 'Training', 'Behavioral Trainer'),

  ('Esha AS', 'Training', 'Behavioral Trainer'),

  ('Muhammad SAW', 'Training', 'Life Science');

--Message: 4 Rows Affected

--Output

select * from tabEmployee

1000   Neha Yadav          Development    Analyst Trainee     25000

1001   Shalini Patel       Development    Analyst Trainee     25000

1002   Abinesh Thangaraj   Development    Analyst Trainee     25000

1003   Syed Nawas          Development    Technical Trainer    9000

1004   Sameeha Syed        Development    Technical Trainer    9000
1005   Saleema Parvin      NULL           Technical Trainer    9000
1006   Asrafi Basha        Training       NULL                 9000
1007   Adham AS            Training       Behavioral Trainer   9000
1008   Ibrahim AS          Training       Behavioral Trainer   9000
1009      Esha AS             Training       Behavioral Trainer   9000
1010      Muhammad SAW        Training       Life Science                   9000