Comment changer les valeurs de colonne d'identité par programmation?

Question

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

J'ai les lignes de cette table, et tous ont leur correspondant ID autoincremented valeur.

Maintenant j'aimerais changer pour chaque ID dans ce tableau comme ceci:

ID = ID + 1

Mais quand je fais ceci, j'obtiens une erreur:

Cannot update identity column 'ID'.

J'ai essayé ceci:

ALTER TABLE Test NOCHECK CONSTRAINT ALL 
set identity_insert ID ON

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

J'ai besoin d'avoir l'identité de définir cette colonne, mais j'ai besoin de changer les valeurs comme bien de temps en temps. Donc ma question est de savoir comment accomplir cette tâche.

Answer 1

Vous avez besoin de

set identity_insert YourTable ON

Puis de les supprimer de votre ligne de la et réinsérez-la avec une identité différente.

Une fois que vous avez terminé la plaquette ne pas oublier de désactiver identity_insert off

set identity_insert YourTable OFF

Answer 2

IDENTITY des valeurs de la colonne sont immuables.

Toutefois, il est possible de changer les métadonnées de la table à supprimer l' IDENTITY de la propriété, de faire la mise à jour, puis revenir.

En supposant que 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'

Ensuite, 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 un auto-incrémentation de la colonne qui peuvent également être mis à jour plus simple de le faire 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 le biais de l'INTERFACE utilisateur dans SQL Server 2005 gestionnaire de changer la colonne de supprimer la numérotation automatique (l'identité) de la propriété de la colonne (sélectionnez la table par un clic droit dessus et choisissez "Design").

Puis d'exécuter votre requête:

UPDATE table SET Id = Id + 1

Puis ajouter la numérotation automatique des biens de retour de la colonne.

Answer 4

Tout d'abord le réglage de IDENTITY_INSERT sur on ou off pour que la matière ne fonctionnera pas pour ce que vous avez besoin (il est utilisé pour l'introduction de nouvelles valeurs, telles que de brancher les lacunes).

Faire l'opération par le biais de l'interface graphique crée une table temporaire, des copies de toutes les données dans une nouvelle table sans un champ d'identité, et renomme la table.

Answer 5

Cela peut être fait à l'aide d'une table temporaire.

L'idée

• désactiver les contraintes (dans le cas où votre identité est référencée par une clé étrangère)
• créer une table temporaire avec le nouvel id
• supprimer le contenu de la table
• copie de sauvegarde des données à partir de la copie d'un tableau à votre table d'origine
• activer previsously désactivé contraintes

Les Requêtes SQL

Disons que votre test tableau deux colonnes supplémentaires (column2 et column3) et qu'il y a 2 tables ayant des clés étrangères référencement test appelés foreign_table1 et 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.