Summary on 20080701: insert value for identity column in sql

In MSSQLServer, by default, user can‘t insert/assign/update value for identity column; SQLServer will do this for user automatically, so there is one problem caused that: what will do if  wants to assign value for identity column? for one table like: teacher(teacher_id, teacher_name, teacher_age), and teacher_id is identity the insert should be like: insert into teacher values('kalash',12), that's correct format, if insert is like: insert into teacher values(1,'kalash',12), there will be error when execute the statement

and for the case that user want to insert value for identity column, user can turn on the identity_insert for implicitly insert, the code is as followed:

set identity_insert [table_name] on

insert into teacher(teacher_id, teacher_name)
values(1,'klasch');

set identity_insert [table_name] off

for the above code, please note that: 1. for the insert, the column name should be listed out, especially the 'teacher_id' 2. any time, the 'identity_insert' can be set to 'on' for only one table, if the 'identity_insert' is set to 'on' and set that for another table, there will be error, so for tip3 3. it's suggested that every time after set the identity_insert to be 'on', set it back to 'off' after the operation