Явное значение столбца идентификатора в таблице может быть указано только при использовании списка столбцов, а IDENTITY_INSERT – в SQL Server

Вопрос: Я пытаюсь сделать этот запрос INSERT INTO dbo.tbl_A_archive SELECT * FROM SERVER0031.DB.dbo.tbl_A но даже после того, как я побежал set identity_insert dbo.tbl_A_archive on Я получаю это сообщение об ошибке Явное значение для столбца идентификаторов в таблице 'dbo.tbl_A_archive' может быть указано только в том случае, если используется список столбцов, а IDENTITY_INSERT включен. tbl_A - это

Вопрос:

Я пытаюсь сделать этот запрос

INSERT INTO dbo.tbl_A_archive SELECT * FROM SERVER0031.DB.dbo.tbl_A

но даже после того, как я побежал

set identity_insert dbo.tbl_A_archive on

Я получаю это сообщение об ошибке

Явное значение для столбца идентификаторов в таблице ‘dbo.tbl_A_archive’ может быть указано только в том случае, если используется список столбцов, а IDENTITY_INSERT включен.

tbl_A – это огромная таблица в строках и ширине, то есть в ней много столбцов. Я не хочу печатать все столбцы вручную. Как я могу заставить это работать?

Лучший ответ:

Ну, сообщение об ошибке в основном говорит все. У вас есть следующие возможности:

  • Создайте список столбцов (SELECT на INFORMATION_SCHEMA.COLUMNS и хороший текстовый редактор или решения, предложенные Andomar и Dave, могут помочь вам в этом)

ИЛИ

  • введите столбец идентификатора в tbl_A_archive регулярный (не идентичный) int столбец (так как это таблица архива, для чего вам нужен столбец идентификатора?).

Ответ №1SET IDENTITY_INSERT tableA ON

Вам нужно сделать список столбцов для инструкции INSERT:

INSERT Into tableA ([id], [c2], [c3], [c4], [c5] ) SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

не нравится “INSERT Inta tableA SELECT……..”

SET IDENTITY_INSERT tableA OFF Ответ №2

Если вы используете SQL Server Management Studio, вам не нужно вводить список столбцов самостоятельно – просто щелкните таблицу в обозревателе объектов правой кнопкой мыши и выберите Script Таблица как ВЫБРАТЬ в Окно редактора новых запросов.

Если вы этого не сделаете, то в качестве отправной точки должен помочь запрос, похожий на этот:

SELECT SUBSTRING( (SELECT ‘, ‘ + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘tbl_A’ ORDER BY ORDINAL_POSITION FOR XML path(»)), 3, 200000); Ответ №3

Согласитесь с ответом Хайнци. Для параметра “Первая секунда” здесь возникает запрос, который генерирует список столбцов в таблице, разделенный запятыми:

select name + ‘, ‘ as [text()] from sys.columns where object_id = object_id(‘YourTable’) for xml path(»)

Для больших таблиц это может сэкономить много печатной работы:)

Ответ №4

Если таблица “архив” предназначена для точной копии вашей основной таблицы, я бы просто предложил удалить тот факт, что идентификатор является столбцом идентификатора. Таким образом, вы сможете вставить их.

В качестве альтернативы вы можете разрешить и вставки идентификатора запрета для таблицы со следующим утверждением

SET IDENTITY_INSERT tbl_A_archive ON —Your inserts here SET IDENTITY_INSERT tbl_A_archive OFF

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

sp_columns tbl_A_archive

Это вернет вам все столбцы из таблицы, которые вы затем можете вырезать и вставить в свой запрос. (Это почти ВСЕГДА лучше, чем использование *)

Ответ №5

Для оператора SQL вам также необходимо указать список столбцов. Например,

INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)

вместо

INSERT INTO tbl VALUES ( value1,value2) Ответ №6

Оба будут работать, но если вы по-прежнему получаете сообщение об ошибке # 1, тогда переходите к # 2

1)

SET IDENTITY_INSERT customers ON GO insert into dbo.tbl_A_archive(id, …) SELECT Id, … FROM SERVER0031.DB.dbo.tbl_A

2)

SET IDENTITY_INSERT customers ON GO insert into dbo.tbl_A_archive(id, …) VALUES(@Id,….) Ответ №7

Чтобы заполнить все имена столбцов в список с разделителями-запятыми для оператора Select для решений, упомянутых для этого вопроса, я использую следующие параметры, поскольку они немного менее подробные, чем Andomar’s. Однако Андомар все же вполне приемлем.

1)

SELECT column_name + ‘,’ FROM information_schema.columns WHERE table_name = ‘YourTable’

2) Это, вероятно, самый простой способ создания столбцов,
если у вас есть SSMS SQL Server.

1) Перейдите в таблицу в Проводнике объектов и щелкните по значку + слева от имени таблицы или дважды щелкните имя таблицы, чтобы открыть список.

2) Перетащите подпапку столбца в основную область запроса, и она автоматически заполнит весь список столбцов.

Ответ №8

Вам нужно указать имя столбца, которое вы хотите вставить, если есть столбец Identity.
Таким образом, команда будет выглядеть следующим образом:

SET IDENTITY_INSERT DuplicateTable ON INSERT Into DuplicateTable ([IdentityColumn], [Column2], [Column3], [Column4] ) SELECT [IdentityColumn], [Column2], [Column3], [Column4] FROM MainTable SET IDENTITY_INSERT DuplicateTable OFF

Если ваша таблица имеет много столбцов, введите имя столбца с помощью этой команды.

SELECT column_name + ‘,’ FROM information_schema.columns WHERE table_name = ‘TableName’ for xml path(»)

(после удаления последней запятой (‘,’)) Просто скопируйте имя столбца.

Ответ №9

Это должно работать. Я просто столкнулся с вашей проблемой:

SET IDENTITY_INSERT dbo.tbl_A_archive ON; INSERT INTO dbo.tbl_A_archive (IdColumn,OtherColumn1,OtherColumn2,…) SELECT * FROM SERVER0031.DB.dbo.tbl_A; SET IDENTITY_INSERT dbo.tbl_A_archive OFF;

К сожалению, кажется, вам нужен список столбцов, включая столбец идентификаторов, чтобы вставить записи, в которых указан идентификатор. Тем не менее, вы не должны перечислять столбцы в SELECT. Как и предполагал @Dave Cluderay, это приведет к тому, что вы сможете скопировать и вставить отформатированный список (если меньше 200000 символов).

Я добавил ИСПОЛЬЗОВАНИЕ, так как я переключаюсь между экземплярами.

USE PES SELECT SUBSTRING( (SELECT ‘, ‘ + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘Provider’ ORDER BY ORDINAL_POSITION FOR XML path(»)), 3, 200000); Ответ №10

Этот фрагмент кода показывает, как вставить в таблицу, когда для столбца первичного ключа идентификатора установлено значение ON.

SET IDENTITY_INSERT [dbo].[Roles] ON GO insert into Roles (Id,Name) values(1,’Admin’) GO insert into Roles (Id,Name) values(2,’User’) GO SET IDENTITY_INSERT [dbo].[Roles] OFF GO Ответ №11

Если вы хотите вставить свои значения из одной таблицы в другую через хранимую процедуру. Я использовал этот и этот, последний, который почти как ответ Andomar.

CREATE procedure [dbo].[RealTableMergeFromTemp] with execute as owner AS BEGIN BEGIN TRANSACTION RealTableDataMerge SET XACT_ABORT ON DECLARE @columnNameList nvarchar(MAX) = STUFF((select ‘,’ + a.name from sys.all_columns a join sys.tables t on a.object_id = t.object_id where t.object_id = object_id(‘[dbo].[RealTable]’) order by a.column_id for xml path (») ),1,1,») DECLARE @SQLCMD nvarchar(MAX) =N’INSERT INTO [dbo].[RealTable] (‘ + @columnNameList + N’) SELECT * FROM [#Temp]’ SET IDENTITY_INSERT [dbo].[RealTable] ON; exec(@sqlcmd) SET IDENTITY_INSERT [dbo].[RealTable] OFF COMMIT TRANSACTION RealTableDataMerge END GO Ответ №12SET IDENTITY_INSERT tableA ON INSERT Into tableA ([id], [c2], [c3], [c4], [c5] ) SELECT [id], [c2], [c3], [c4], [c5] FROM tableB

Не так

INSERT INTO tableA SELECT * FROM tableB SET IDENTITY_INSERT tableA OFF Ответ №13

Это изображение показывает, как вставить в таблицу, когда столбец первичного ключа удостоверения включен.

Ответ №14

Я думаю, что эта ошибка возникает из-за несоответствия количества столбцов в определении таблицы и количества столбцов в запросе вставки. Также длина столбца опускается с введенным значением. Так что просто просмотрите определение таблицы, чтобы решить эту проблему

Оцените статью
Добавить комментарий