Comment changer les valeurs d'une colonne d'identité de manière programmatique ?

Question

J'ai une base de données MS SQL 2005 avec une table Test avec colonne ID . ID est une colonne d'identité.

J'ai des lignes dans ce tableau et toutes ont leur ID correspondant auto incrémenté.

Maintenant je voudrais changer chaque ID dans cette table comme ceci :

ID = ID + 1

Mais lorsque je fais cela, j'obtiens une erreur :

Impossible de mettre à jour la colonne d'identité 'ID'.

J'ai essayé ça :

    ALTER TABLE Test NOCHECK CONSTRAINT ALL 
    set identity_insert ID ON

Mais cela ne résout pas le problème.

Je dois définir l'identité de cette colonne, mais je dois aussi changer les valeurs de temps en temps. Ma question est donc de savoir comment accomplir cette tâche.

Answer 1

Vous devez

set identity_insert YourTable ON

Ensuite, supprimez votre ligne et réinsérez-la avec une identité différente.

Une fois que vous avez effectué l'insertion, n'oubliez pas de désactiver identity_insert.

set identity_insert YourTable OFF

Answer 2

IDENTITY Les valeurs des colonnes sont immuables.

Il est toutefois possible de modifier les métadonnées de la table afin de supprimer l'indicateur de qualité de l'eau. IDENTITY propriété, faire la mise à jour, puis revenir en arrière.

En supposant la structure suivante

CREATE TABLE Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
X VARCHAR(10)
)

INSERT INTO Test 
OUTPUT INSERTED.*
SELECT 'Foo' UNION ALL
SELECT 'Bar' UNION ALL
SELECT 'Baz'

Alors vous pouvez faire

/*Define table with same structure but no IDENTITY*/
CREATE TABLE Temp
(
ID INT PRIMARY KEY,
X VARCHAR(10)
)

/*Switch table metadata to new structure*/
ALTER TABLE Test SWITCH TO Temp;

/*Do the update*/
UPDATE Temp SET ID = ID + 1;

/*Switch table metadata back*/
ALTER TABLE Temp SWITCH TO Test;

/*ID values have been updated*/
SELECT *
FROM Test

/*Safety check in case error in preceding step*/
IF NOT EXISTS(SELECT * FROM Temp)
    DROP TABLE Temp /*Drop obsolete table*/

Dans SQL Server 2012, il est possible d'avoir une colonne à incrémentation automatique qui peut aussi être mise à jour plus simplement avec SEQUENCES

CREATE SEQUENCE Seq
    AS INT
    START WITH 1
    INCREMENT BY 1

CREATE TABLE Test2
(
ID INT DEFAULT NEXT VALUE FOR Seq NOT NULL PRIMARY KEY,
X VARCHAR(10)
)

INSERT INTO Test2(X)
SELECT 'Foo' UNION ALL
SELECT 'Bar' UNION ALL
SELECT 'Baz'

UPDATE Test2 SET ID+=1

Answer 3

Par l'intermédiaire de l'interface utilisateur du gestionnaire SQL Server 2005, modifiez la colonne en supprimant la propriété autonumber (identité) de la colonne (sélectionnez la table en faisant un clic droit dessus et choisissez "Design").

Ensuite, exécutez votre requête :

UPDATE table SET Id = Id + 1

Ensuite, ajoutez la propriété autonumber à la colonne.

Answer 4

Tout d'abord, l'activation ou la désactivation de l'option IDENTITY_INSERT ne répondra pas à vos besoins (elle est utilisée pour insérer de nouvelles valeurs, par exemple pour combler des lacunes).

En effectuant l'opération via l'interface graphique, on crée simplement une table temporaire, on copie toutes les données dans une nouvelle table sans champ d'identité, et on renomme la table.

Answer 5

Cela peut être fait en utilisant une table temporaire.

L'idée

• désactiver les contraintes (dans le cas où votre id est référencé par une clé étrangère)
• créer une table temporaire avec le nouvel identifiant
• supprimer le contenu de la table
• recopier les données de la table copiée vers votre table originale
• activer les contraintes désactivées à l'avance

Requêtes SQL

Disons que votre test ont deux colonnes supplémentaires ( column2 y column3 ) et qu'il y a 2 tables ayant des clés étrangères référençant test appelé foreign_table1 y foreign_table2 (parce que les problèmes de la vie réelle ne sont jamais simples).

alter table test nocheck constraint all;
alter table foreign_table1 nocheck constraint all;
alter table foreign_table2 nocheck constraint all;
set identity_insert test on;

select id + 1 as id, column2, column3 into test_copy from test v;
delete from test;
insert into test(id, column2, column3)
select id, column2, column3 from test_copy

alter table test check constraint all;
alter table foreign_table1 check constraint all;
alter table foreign_table2 check constraint all;
set identity_insert test off;
drop table test_copy;

C'est tout.