Вопрос:
Я пытаюсь сделать этот запрос
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
Я думаю, что эта ошибка возникает из-за несоответствия количества столбцов в определении таблицы и количества столбцов в запросе вставки. Также длина столбца опускается с введенным значением. Так что просто просмотрите определение таблицы, чтобы решить эту проблему
