设定会话级参数identity_insert(控制自动增长)

sqlerver 批量插入记录时,对有标识列的字段要设置 set IDENTITY_INSERT 表名 on,然后再执行插入记录操作;插入完毕后恢复为 off 设置

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

举例:

set IDENTITY_INSERT AreaFull on
insert into AreaFull(AreaId, AreaName, AreaPid) values(1, N'北京市', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(2, N'天津市', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(3, N'河北省', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(4, N'山西省', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(5, N'内蒙古自治区', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(6, N'辽宁省', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(7, N'吉林省', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(8, N'黑龙江省', 0);
insert into AreaFull(AreaId, AreaName, AreaPid) values(9, N'上海市', 0);
go
set IDENTITY_INSERT AreaFull 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