SQL Server, comment définir auto increment après avoir créé une table sans perte de données?
j'ai une table table1 dans SQL server 2008 et il y a des enregistrements dedans.
je veux que la colonne de la clé primaire table1_Sno soit une colonne à incrémentation automatique. Peut-on le faire sans aucun transfert de données ou clonage de table?
je sais que je peux utiliser ALTER TABLE pour ajouter une colonne auto-increment, mais puis-je simplement ajouter L'option AUTO_ increment à une colonne existante qui est la clé primaire?
7 réponses
changer la propriété IDENTITY est en fait un changement de métadonnées seulement. Mais pour mettre à jour les métadonnées directement, il faut démarrer l'instance en mode mono-utilisateur et contourner certaines colonnes dans sys.syscolpars et est non documentée/non supportée et pas quelque chose que je recommanderais ou ne donnerai pas de détails supplémentaires à ce sujet.
pour les personnes venant à travers cette réponse sur SQL Server 2012+ de loin la manière la plus facile d'atteindre ce résultat d'une incrémentation automatique la colonne serait de créer un objet SEQUENCE et de définir le next value for seq comme la colonne par défaut.
alternativement, ou pour les versions précédentes (à partir de 2005), la solution de contournement affichée sur cet article de connexion montre une façon entièrement supportée de le faire sans besoin de taille des opérations de données en utilisant ALTER TABLE...SWITCH . Aussi blogué sur MSDN ici . Bien que le code pour y parvenir n'est pas très simple et il ya restriction - comme le tableau modifié ne peut pas être la cible d'une contrainte de clé étrangère.
exemple de code.
mettre en place une table d'essai sans colonne identity .
CREATE TABLE dbo.tblFoo
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)
INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2
Modifier pour avoir une colonne identity (plus ou moins instantanée).
BEGIN TRY;
BEGIN TRANSACTION;
/*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
set the correct seed in the table definition instead*/
DECLARE @TableScript nvarchar(max)
SELECT @TableScript =
'
CREATE TABLE dbo.Destination(
bar INT IDENTITY(' +
CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1) PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)
ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
'
FROM dbo.tblFoo
WITH (TABLOCKX,HOLDLOCK)
EXEC(@TableScript)
DROP TABLE dbo.tblFoo;
EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH;
tester le résultat.
INSERT INTO dbo.tblFoo (filler,filler2)
OUTPUT inserted.*
VALUES ('foo','bar')
donne
bar filler filler2
----------- --------- ---------
10001 foo bar
Nettoyage
DROP TABLE dbo.tblFoo
SQL Server: comment définir auto-increment sur une table avec des lignes en elle:
cette stratégie copie physiquement les lignes deux fois, ce qui peut prendre beaucoup plus de temps si la table que vous copiez est très grande.
vous pourriez sauver vos données, laisser tomber et reconstruire la table avec l'auto-incrément et la clé primaire, puis Charger les données de nouveau.
je vais vous montrer un exemple:
Étape 1, Créer la barre de pied de table (sans clé primaire ou auto-incrément):
CREATE TABLE foobar(
id int NOT NULL,
name nchar(100) NOT NULL,
)
Étape 2, insérer quelques lignes
insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');
Étape 3, Copier les données du fobar dans une table de température:
select * into temp_foobar from foobar
Étape 4, drop table foobar:
drop table foobar;
Étape 5, recréer votre table avec la clé primaire et les propriétés d'auto-incrément:
CREATE TABLE foobar(
id int primary key IDENTITY(1, 1) NOT NULL,
name nchar(100) NOT NULL,
)
Étape 6, insérez vos données de la table de température dans le pied de page
SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;
Étape 7, déposez votre table de température, et vérifiez si elle a fonctionné:
drop table temp_foobar;
select * from foobar;
vous devriez obtenir ceci, et quand vous inspectez la table de pied de page, la colonne id est auto-increment de 1 et id est une clé primaire:
1 one
2 two
3 three
si vous voulez le faire via le concepteur, vous pouvez le faire en suivant les instructions ici "enregistrer les modifications n'est pas autorisé" lorsque vous changez une colonne existante pour être null
si vous ne voulez pas ajouter une nouvelle colonne, et que vous pouvez garantir que votre colonne int actuelle est unique, vous pouvez sélectionner toutes les données dans une table temporaire, laisser tomber la table et recréer avec la colonne identité spécifiée. Ensuite, en utilisant SET IDENTITY INSERT ON vous pouvez insérer toutes vos données dans la table temporaire dans la nouvelle table.
Oui, vous pouvez. Allez à outils > concepteurs > Table et concepteurs et décochez "évitez D'enregistrer les changements qui empêchent la récréation de la Table" .
script ci-dessous peut être une bonne solution.Il travaillait aussi dans les données volumineuses.
ALTER DATABASE WMlive SET RECOVERY SIMPLE WITH NO_WAIT
ALTER TABLE WMBOMTABLE DROP CONSTRAINT PK_WMBomTable
ALTER TABLE wmbomtable drop column BOMID
ALTER TABLE WMBOMTABLE ADD BomID int IDENTITY (1, 1) NOT NULL;
ALTER TABLE WMBOMTABLE ADD CONSTRAINT PK_WMBomTable PRIMARY KEY CLUSTERED (BomID);
ALTER DATABASE WMlive SET RECOVERY FULL WITH NO_WAIT