[SQL Server] [basic] how to assign values to auto-increment columns during insert

-- Create a test environment
If object_id ('tb') is not null drop table TB
Go
Create Table TB
(
Id int identity,
Code varchar (10 ),
Name varchar (20 ),
Constraint pk_tb primary key (ID)
)
Go
-- Query
Insert TB select 'A', 'A'
Insert TB values ('bb ', 'bb ')
Select * from TB
Go
-- Result
/*
ID code name
-----------------------------------------
1 aa
2 bb

(2 rows affected)
*/
Set identity_insert TB on
Insert TB select 3, 'A', 'A'
Insert TB values (3, 'bb', 'bb ')
Go
-- Result
/*
Message 8101, level 16, state 1, 6th rows
Only when the column list is used and identity_insert is on can an explicit value be specified for the ID column in The 'tb' table.
Message 8101, level 16, state 1, 7th rows
Only when the column list is used and identity_insert is on can an explicit value be specified for the ID column in The 'tb' table.
*/
Set identity_insert TB on
Insert Tb (ID, code, name) Select 3, 'A', 'A'
Insert Tb (ID, code, name) values (4, 'bb ', 'bb ')
Select * from TB
-- Result
/*
ID code name
-----------------------------------------
1 aa
2 bb
3 aa
4 BB

(Four rows affected)
*/