[轉載] set IDENTITY_INSERT on 和 off 的設定

sqlserver 批量插入記錄時,對有識別欄位的欄位要設定 set IDENTITY_INSERT 表名 on,然後再執行插入記錄操作;插入完畢後恢複為 off 設定

格式:
  set IDENTITY_INSERT 表名 on
  set IDENTITY_INSERT 表名 offf

舉例:

set IDENTITY_INSERT peoplePworkpositiontype  on
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(1 , '平台' , 1 ) 
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(2 , '陸地' , 2 ) 
insert peoplePworkpositiontype(id,workpositiontype,workpositiontypeid) values(3 , '海上' , 3 )

go
set IDENTITY_INSERT peoplePworkpositiontype  off

set IDENTITY_INSERT peoplePstatetype  on
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(1 , '出海' , 1 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(2 , '出差' , 2 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(3 , '公司' , 3 ) 
insert peoplePstatetype(id,nowstatetype,nowstatetypeid) values(4 , '會議' , 4 ) 
go
set IDENTITY_INSERT peoplePstatetype  off
go