Une valeur explicite pour la colonne d'identité dans la table ne peut être spécifiée que lorsqu'une liste de colonnes est utilisée et que IDENTITY_INSERT est ON SQL Server

Question

J'essaie de faire cette requête

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

mais même après avoir couru

set identity_insert dbo.tbl_A_archive on

Je reçois ce message d'erreur

Une valeur explicite pour la colonne d'identité dans la table 'dbo.tbl_A_archive' ne peut être spécifiée que lorsqu'une liste de colonnes est utilisée et que IDENTITY_INSERT est ON.

tbl_A est un tableau énorme en lignes et en largeur, c'est-à-dire qu'il comporte BEAUCOUP de colonnes. Je ne veux pas avoir à taper toutes les colonnes manuellement. Comment faire pour que cela fonctionne ?

Answer 1

SET IDENTITY_INSERT tableA ON

Vous devez établir une liste de colonnes pour votre instruction INSERT :

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

pas comme "INSERT Into tableA SELECT ........"

SET IDENTITY_INSERT tableA OFF

Answer 2

Résumé

SQL Server ne vous laisse pas insérer une valeur explicite dans une colonne d'identité à moins que vous n'utilisiez une liste de colonnes. Ainsi, vous avez les options suivantes :

1. Faites une liste de colonnes (manuellement ou à l'aide d'outils, voir ci-dessous).

OU

2. faire la colonne d'identité dans tbl_A_archive un régulier, non-identité colonne : Si votre table est une table d'archives et que vous spécifiez toujours une valeur explicite pour la colonne d'identité, pourquoi avez-vous besoin d'une colonne d'identité ? Utilisez simplement un int normal à la place.

---

Détails sur la solution 1

Au lieu de

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table
  SELECT *
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

vous devez écrire

SET IDENTITY_INSERT archive_table ON;

INSERT INTO archive_table (field1, field2, ...)
  SELECT field1, field2, ...
  FROM source_table;

SET IDENTITY_INSERT archive_table OFF;

avec field1, field2, ... contenant les noms de toutes les colonnes de vos tableaux. Si vous voulez générer automatiquement cette liste de colonnes, jetez un coup d'oeil à Réponse de Dave o Réponse d'Andomar .

---

Détails sur la solution 2

Malheureusement, il n'est pas possible de simplement "changer le type" d'une colonne int d'identité en une colonne int de non-identité. En gros, vous avez les options suivantes :

• Si le tableau d'archives ne contient pas encore de données, supprimez la colonne et ajoutez-en une nouvelle sans identité.

OU

• Utilisez SQL Server Management Studio pour définir le Identity Specification / (Is Identity) de la colonne d'identité dans votre table d'archives pour No . Dans les coulisses, cela créera un script pour recréer la table et copier les données existantes, donc, pour ce faire, vous devrez également désactivez Tools / Options / Designers / Table and Database Designers / Prevent saving changes that require table re-creation .

OU

• Utilisez l'une des solutions de contournement décrites dans cette réponse : Supprimer l'identité d'une colonne dans une table

Answer 3

Si vous utilisez SQL Server Management Studio, vous n'avez pas besoin de taper la liste des colonnes vous-même - il suffit de cliquer avec le bouton droit de la souris sur la table dans la section Explorateur d'objets et choisissez script Tableau comme -> SELECT à -> Fenêtre de l'éditeur de nouvelles requêtes .

Si ce n'est pas le cas, une requête similaire à celle-ci devrait vous servir de point de départ :

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

Answer 4

Je suis d'accord avec la réponse de Heinzi. Pour la première deuxième option, voici une requête qui génère une liste de colonnes séparées par des virgules dans une table :

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

Pour les grandes tables, cela peut permettre d'économiser beaucoup de travail de saisie :)

Answer 5

Si la table "archive" est censée être une copie exacte de votre table principale, je vous suggère simplement de supprimer le fait que l'identifiant est une colonne d'identité. De cette façon, vous pourrez les insérer.

Alternativement, vous pouvez autoriser et interdire les insertions d'identité pour la table avec l'instruction suivante

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

Enfin, si vous avez besoin que la colonne d'identité fonctionne telle quelle, vous pouvez toujours simplement exécuter la procédure stockée.

sp_columns tbl_A_archive 

Vous obtiendrez ainsi toutes les colonnes de la table, que vous pourrez ensuite couper et coller dans votre requête. (C'est presque TOUJOURS mieux que d'utiliser un *)