Невозможно вставить явное значение, потому что IDENTITY_INSERT выключен, но не может установить ID IDTITITY_INSERT в ON, поскольку он уже включен

У меня есть таблица в базе данных Foo с именем Bar, у которой есть столбец с именем ID, который является первичным ключом, и эта база данных находится в разработке SQL Server.

Я пытаюсь скопировать данные с нашего производственного сервера на сервер разработки, чтобы я мог играть с указанными данными, поэтому я выполняю следующее:

set IDENTITY_INSERT Foo.dbo.Bar ON
insert into Foo.dbo.Bar
(
   ID
   ,Something
   ,Else
   ,Is
   ,Going
   ,Horribly
   ,Wrong
   ,With
   ,SQL
)
select 
  ID
  ,Something
  ,Else
  ,Is
  ,Going
  ,Horribly
  ,Wrong
  ,With
  ,SQL
from Production.Foo.dbo.Bar

set IDENTITY_INSERT Foo.dbo.Bar OFF

И я получаю сообщение об ошибке

Msg 8107, уровень 16, состояние 1, строка 1
IDENTITY_INSERT уже включен для таблицы 'Foo.dbo.Bar'. Невозможно выполнить операцию SET для таблицы "Foo.dbo.Bar".

Хмм..хорошо, поэтому IDENTITY_INSERT включен для таблицы. Поэтому я удалил SET IDENTITY_INSERT Foo.dbo.Bar ON из верхней части моего запроса, а затем выполнил его, и я получил эту ошибку:

Msg 544, уровень 16, состояние 1, строка 1
Невозможно вставить явное значение для столбца идентификатора в таблице "Bar", если для параметра IDENTITY_INSERT установлено значение OFF.

Я могу выполнить SET IDENTITY_INSERT Foo.dbo.Bar OFF весь день, но если я попытаюсь повернуть его ON, тогда SQL Server 2012 говорит, что IDENTITY_INSERT уже включен.

Ответ 1

Funny - у меня была точно такая же проблема с 3 из 4 таблиц в моей новой базе данных ss2012.

Итак, я переписал таблицу create script, чтобы скопировать мои вставки с помощью "set identity on" и "set identity off", и он работает. Кажется, что ss только хочет обрабатывать один идентификатор таблицы за раз, т.е. вы не можете установить новый идентификатор таблицы до тех пор, пока все остальные не будут отключены.

что-то вроде этого:

create table Employers
(
id int PRIMARY KEY IDENTITY ,
companyid nvarchar(50) not null,
companyName nvarchar(80) not null,
address nvarchar(80),
Phone nvarchar(10),
);

SET IDENTITY_INSERT employers ON;

insert into Employers(id,companyid,companyName,address,Phone)
Values
(...),
(...)

SET IDENTITY_INSERT employers OFF;

create table customers
(
...

Ответ 2

На самом деле я нашел другое решение. Это на самом деле отличается от Msg 8101, для того чтобы идентификатор таблицы не был установлен в положение ON.

Чтобы устранить проблему, вам нужно отбросить схему и таблицы с теми же именами в основной базе данных.

У меня есть шаблон script, который создает базу данных и таблицы и вставляет данные в эти таблицы, например:

USE master
GO
CREATE DATABASE [mydb] ON  PRIMARY 
( NAME = N'mydb', FILENAME = N'C:\SQLDATA\mydb.mdf' , SIZE = 5072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'mydb_log', FILENAME = N'C:\SQLDATA\mydb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

USE [mydb]
GO

CREATE SCHEMA [myschema] AUTHORIZATION [dbo]
GO

CREATE TABLE [myschema].[mytable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SOMETHING] [int] NOT NULL,
CONSTRAINT [PK_mytable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
)
GO

SET IDENTITY_INSERT [myschema].[mytable] ON 
GO

INSERT [myschema].[mytable] ([ID], [SOMETHING]) VALUES (1,2)
GO

SET IDENTITY_INSERT [myschema].[mytable] OFF
GO

Проблема заключалась в том, что во время создания базы данных утверждение завершилось неудачно из-за ошибки "активации устройства" (разрешения для папки, в которой будет создан файл MDF) или из-за размера файла базы данных. script продолжал, но вместо этого создал схемы и таблицы в основной базе данных. После того, как я понял ошибку, я остановил script и попытался запустить ее снова с исправленными проблемами.

Тогда я получил ошибку.

Msg 8107, Level 16, State 1, Line 2
IDENTITY_INSERT is already ON for table 'master.myschema.mytable'. Cannot perform SET operation for table 'myschema.mytable'.
Msg 544, Level 16, State 1, Line 2
Cannot insert explicit value for identity column in table 'mytable' when IDENTITY_INSERT is   set to OFF.

Я не эксперт SQL Server, но я думаю, что сначала он ищет схему в основной базе данных, и потому что эта схема найдена, и в текущем сеансе IDENTITY уже включен, он выдает это сообщение об ошибке.

После удаления схем и таблиц, неправильно созданных в основной базе данных, мой script успешно запущен.

Ответ 3

У меня была такая же ошибка. Я использовал linq-to-sql, и файл таблицы и dbml был первоначально сгенерирован без столбца идентификатора первичного ключа.
Позднее я обновил первичный ключ в столбце Identity, но забыл обновить файл dbml.

Невозможно вставить явное значение для столбца идентификатора в таблице "BLAH", если для параметра IDENTITY_INSERT установлено значение OFF

Исправлено было обновление файла dbml достаточно забавно...