在标识列中插入值0

I am trying to insert a value of 0 into a column of a table which has existing data. The identity column is (1,1). Do I need to delete the existing data in order to insert a value of 0?

我试图将值0插入到具有现有数据的表的列中。标识列是（1,1）。我是否需要删除现有数据才能插入值0？

SET IDENTITY_INSERT table ON
INSERT INTO table (id,...) Values(0,.....)
SET IDENTITY_INSERT table OFF

I tried to run this but the script just hangs.

我试图运行它，但脚本只是挂起。

3 个解决方案

#1

You can do this using set identity_insert table on

您可以使用set identity_insert表来执行此操作

create table #test (id int identity(1,1), val int)

insert into #test (val) values (10),(11)

set identity_insert #test on

insert into #test (id, val) values (0,12)

set identity_insert #test off

But if you have PK or Unique index on id then you will get constraint error

但是，如果您在id上有PK或唯一索引，那么您将获得约束错误

#2

You have to specify the column names for the insert when using identity_insert.

使用identity_insert时，必须指定插入的列名。

create table t (id int not null identity(1,1), col varchar(32));
set identity_insert dbo.t on;
insert into dbo.t (id,col) values(0,'test');
set identity_insert dbo.t off;

rextester demo: http://rextester.com/VMT42836

rextester演示：http：//rextester.com/VMT42836

returns

回报

+----+------+
| id | col  |
+----+------+
|  0 | test |
+----+------+

#3

create table #test (id int identity(0,1), val int)
SET IDENTITY_INSERT #test ON
INSERT INTO #test (id,val) Values(0,100)
SET IDENTITY_INSERT #test OFF
SELECT * FROM #test

id          val
----------- -----------
0           100

#1