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

Question

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

690 11

sql-server

11 ответов:

Comments

Ничего не найдено.

Heinzi · Accepted Answer · 2010-01-05 13:11:03

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

сделать список столбцов (выберите на INFORMATION_SCHEMA.Колонки и хороший текстовый редактор или решения, предложенные Андомаром и Дэйвом, могут помочь вам в этом)

или

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

Ehsan · Accepted Answer · 2012-08-23 14:08:56

SET IDENTITY_INSERT tableA ON
вы должны сделать список столбцов для вашего оператора INSERT:
INSERT Into tableA ([id], [c2], [c3], [c4], [c5] ) 
SELECT [id], [c2], [c3], [c4], [c5] FROM tableB
не нравится " вставить в таблицу выбрать ........"
SET IDENTITY_INSERT tableA OFF

Dave Cluderay · Accepted Answer · 2012-05-18 15:36:51

Если вы используете SQL Server Management Studio, вам не нужно вводить список столбцов самостоятельно - просто щелкните правой кнопкой мыши таблицу в Обозреватель Объектов и выбрать таблицы скрипт ->выберите ->Новое Окно Редактора Запросов.

Если это не так, то запрос, подобный этому, должен помочь в качестве отправной точки:
SELECT SUBSTRING(
    (SELECT ', ' + QUOTENAME(COLUMN_NAME)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'tbl_A'
        ORDER BY ORDINAL_POSITION
        FOR XML path('')),
    3,
    200000);

Andomar · Accepted Answer · 2010-01-05 13:00:34

согласен с ответом Хайнци. Для первого второго варианта, вот запрос, который генерирует разделенный запятыми список столбцов в таблице:
select name + ', ' as [text()] 
from sys.columns 
where object_id = object_id('YourTable') 
for xml path('')
для больших таблиц это может сэкономить много работы по набору текста:)

Robin Day · Accepted Answer · 2010-01-05 12:51:05

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

в качестве альтернативы вы можете разрешить и запретить вставки идентификаторов для таблицы со следующим утверждением
SET IDENTITY_INSERT tbl_A_archive ON
--Your inserts here
SET IDENTITY_INSERT tbl_A_archive OFF
наконец, если вам нужно, чтобы столбец идентификаторов работал как есть, вы всегда можете просто запустить сохраненный proc.
sp_columns tbl_A_archive 
это вернется вы все столбцы из таблицы, которые затем можно вырезать и вставить в запрос. (Это почти всегда лучше, чем с помощью*)

mohsinmdl · Accepted Answer · 2018-01-07 00:21:40

для инструкции SQL также необходимо указать список столбцов. Например.
INSERT INTO tbl (idcol1,col2) VALUES ( value1,value2)
вместо
INSERT INTO tbl VALUES ( value1,value2)

Ryan · Accepted Answer · 2016-03-31 20:05:38

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

1)
SELECT column_name + ',' 
FROM   information_schema.columns 
WHERE  table_name = 'YourTable'
2) это, вероятно, самый простой подход к созданию столбцов, если у вас есть SQL Server SSMS.

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

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

Chirag Thakar · Accepted Answer · 2016-11-19 06:55:06

оба будут работать, но если вы все еще получаете ошибку с помощью #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,....)
хорошего дня.

Md. Rousonur Jaman · Accepted Answer · 2017-09-08 07:53:19

вы должны указать имя столбцов, которые вы хотите вставить, если есть столбец идентификаторов. Так что команда будет выглядеть следующим образом:
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('')
(после удаления последней запятой (',')) просто скопируйте имя последних столбцов.

Gary · Accepted Answer · 2018-09-14 18:55:42

Это должно работать. Я просто столкнулся с вашей проблемой:
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);

Codemaker · Accepted Answer · 2018-09-08 17:55:43

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