Summary on 20080701: insert value for identity column in sql-优快云博客

本文详细介绍了在SQL Server中如何为标识列手动赋值的方法。默认情况下，SQL Server不允许用户直接为标识列赋值，而是由系统自动分配。若要进行手动赋值，需要启用identity_insert选项，并指定列名进行插入操作。

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