1.把表的IDENTITY_INSERT选项设置为ON
2.插入时要指定相应的列名
示例如下:
1.建表,初始化数据:
create table InsertTable1
(
Id int identity(1,1),
Mark varchar(10)
)
create table InsertTable2
(
Id int identity(1,1),
Mark varchar(10)
)
insert into InsertTable2 values ('a'),('b'),('c'),('d'),('e')
2.把InsertTable2的数据插入InsertTable1中:
set IDENTITY_INSERT InsertTable1 on
insert into InsertTable1 (Id,Mark)
select Id,Mark from InsertTable2
set IDENTITY_INSERT InsertTable1 off
其中IDENTITY_INSERT 的语法为 :
SET IDENTITY_INSERT [ database_name. [ schema_name ] . ] table{ ON | OFF }