Un valor explícito para la columna de identidad en la tabla solo se puede especificar cuando se utiliza una lista de columnas y IDENTITY_INSERT está en ON SQL Server

Estoy tratando de hacer esta consulta

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

pero incluso después de que corrí

 set identity_insert dbo.tbl_A_archive on 

Recibo este mensaje de error

Un valor explícito para la columna de identidad en la tabla ‘dbo.tbl_A_archive’ solo se puede especificar cuando se utiliza una lista de columnas y IDENTITY_INSERT está activado.

tbl_A es una tabla enorme en filas y ancho, es decir, tiene MUCHAS columnas. No quiero tener que escribir todas las columnas de forma manual. ¿Cómo puedo hacer que esto funcione?

Bueno, el mensaje de error básicamente lo dice todo. Usted tiene las siguientes opciones:

  • Haga una lista de columnas (un SELECT en INFORMATION_SCHEMA.COLUMNS y un buen editor de texto o las soluciones propuestas por Andomar y Dave pueden ayudarlo con esto)

O

  • hacer que la columna de identidad en tbl_A_archive una columna tbl_A_archive normal (no identidad) (ya que es una tabla de archivo, ¿por qué necesita una columna de identidad?).
 SET IDENTITY_INSERT tableA ON 

Tienes que hacer una lista de columnas para tu statement INSERT:

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

no me gusta “INSERTAR en la tablaA SELECCIONAR ……..”

 SET IDENTITY_INSERT tableA OFF 

Si está utilizando SQL Server Management Studio, no tiene que escribir la lista de columnas usted mismo, simplemente haga clic con el botón derecho en la tabla en el Explorador de objetos y seleccione Tabla de scripts como -> SELECCIONAR a -> Nueva ventana del editor de consultas .

Si no es así, una consulta similar a esta debería ayudar como punto de partida:

 SELECT SUBSTRING( (SELECT ', ' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tbl_A' ORDER BY ORDINAL_POSITION FOR XML path('')), 3, 200000); 

Estoy de acuerdo con la respuesta de Heinzi. Para la primera segunda opción, aquí hay una consulta que genera una lista de columnas separadas por comas en una tabla:

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

Para mesas grandes, esto puede ahorrar mucho trabajo de tipeo 🙂

Si la tabla de “archivo” pretende ser una copia exacta de su tabla principal, le sugiero que elimine el hecho de que la identificación es una columna de identificación. De esa forma, te permitirá insertarlos.

Alternativamente, puede permitir y no permitir las inserciones de identidad para la tabla con la siguiente statement

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

Finalmente, si necesita que la columna de identidad funcione como está, entonces siempre puede ejecutar el proceso almacenado.

 sp_columns tbl_A_archive 

Esto le devolverá todas las columnas de la tabla que luego puede cortar y pegar en su consulta. (Esto es casi SIEMPRE mejor que usar un *)

Para la instrucción SQL, también debe especificar la lista de columnas. Por ej.

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

en lugar de

 INSERT INTO tbl VALUES ( value1,value2) 

Para llenar todos los nombres de columna en una lista delimitada por comas para una instrucción Select para las soluciones mencionadas para esta pregunta, uso las siguientes opciones, ya que son un poco menos detalladas que las de Andomar. Sin embargo, Andomar todavía es perfectamente aceptable.

1)

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

2) Este es probablemente el enfoque más simple para crear columnas, si tiene SQL Server SSMS.

1) Vaya a la tabla en el Explorador de objetos y haga clic en + a la izquierda del nombre de la tabla o haga doble clic en el nombre de la tabla para abrir la lista secundaria.

2) Arrastra la subcarpeta de la columna al área de consulta principal y te autoperfilará toda la lista de columnas.

Ambos funcionarán, pero si sigues Si obtienes un error al usar # 1 , ve por # 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,....) 

Tenga un buen día.

Debe especificar el nombre de la columna que desea insertar si hay una columna de Identidad. Entonces el comando será así a continuación:

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

Si su tabla tiene muchas columnas, obtenga el nombre de esas columnas usando este comando.

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

(después de eliminar la última coma (‘,’)) Copie el nombre de las columnas pasadas.