Come modificare i valori della colonna Identity in modo programmatico?

Ho un database MS SQL 2005 con una tabella Test con ID colonna. ID è una colonna di id quadro.

Ho righe in questa tabella e tutte hanno il corrispondente valore autoincrementato ID.

Ora vorrei cambiare ogni ID in questa tabella in questo modo:

 ID = ID + 1 

Ma quando faccio questo ottengo un errore:

 Cannot update identity column 'ID'. 

Ho provato questo:

 ALTER TABLE Test NOCHECK CONSTRAINT ALL set identity_insert ID ON 

Ma questo non risolve il problema.

Ho bisogno di avere un’id quadro impostata su questa colonna, ma ho bisogno di cambiare anche i valori di volta in volta. Quindi la mia domanda è come realizzare questo compito.

Devi

 set identity_insert YourTable ON 

Quindi elimina la riga e reinseriscila con un’altra id quadro.

Una volta fatto l’inserimento, non dimenticare di distriggersre identity_insert

 set identity_insert YourTable OFF 

IDENTITY valori delle colonne IDENTITY sono immutabili.

Tuttavia è ansible cambiare i metadati della tabella per rimuovere la proprietà IDENTITY , fare l’aggiornamento, quindi tornare indietro.

Supponendo la seguente struttura

 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' 

Allora puoi farlo

 /*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*/ 

In SQL Server 2012 è ansible avere una colonna autoincrementante che può anche essere aggiornata in modo più diretto con 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 

Tramite l’interfaccia utente nel gestore di SQL Server 2005, modificare la colonna rimuovere la proprietà autonumber (identity) della colonna (selezionare la tabella facendo clic con il pulsante destro del mouse su di essa e selezionare “Design”).

Quindi esegui la tua query:

 UPDATE table SET Id = Id + 1 

Quindi vai e aggiungi la proprietà autonumber alla colonna.

In primo luogo, l’impostazione di IDENTITY_INSERT on o off per quella materia non funzionerà per ciò che desideri (viene utilizzato per l’inserimento di nuovi valori, ad esempio per colmare le lacune).

L’operazione tramite la GUI crea solo una tabella temporanea, copia tutti i dati in una nuova tabella senza un campo identity framework e rinomina la tabella.

Questo può essere fatto usando una tabella temporanea.

L’idea

  • disabilitare i vincoli (nel caso in cui il tuo id sia referenziato da una chiave straniera)
  • crea una tabella temporanea con il nuovo id
  • elimina il contenuto della tabella
  • copia i dati dalla tabella copiata alla tabella originale
  • abilitare i vincoli disabilitati in modo previsivo

Query SQL

Supponiamo che la tabella di test abbia due colonne aggiuntive ( column2 e column3 ) e che ci siano 2 tabelle che hanno test riferimento con chiavi esterne denominate foreign_table1 e foreign_table2 (perché i problemi di vita reale non sono mai semplici).

 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; 

Questo è tutto.

DBCC CHECKIDENT (‘databasename.dbo.orders’, RESEED, 999) è ansible modificare qualsiasi numero di colonna di id quadro con questo comando e inoltre è ansible avviare il numero di quel campo da ogni numero desiderato. Per esempio nel mio comando chiedo di iniziare da 1000 (999 + 1) spero che sia abbastanza … buona fortuna

Se la colonna non è un PK, puoi sempre creare una NUOVA colonna nella tabella con i numeri incrementati, rilasciare l’originale e quindi modificare quello nuovo per essere il vecchio.

curioso sul motivo per cui potrebbe essere necessario farlo … la maggior parte che ho mai avuto da futz con le colonne Identity è stato il backfill dei numeri e ho appena finito di usare DBCC CHECKIDENT (tablename, RESEED, newnextnumber)

in bocca al lupo!

La modifica dell’id quadro potrebbe non riuscire a seconda di un numero di fattori, principalmente ruotando attorno agli oggetti / alle relazioni collegate alla colonna id. Sembra che il design di db sia un problema qui dato che gli id ​​dovrebbero essere cambiati raramente se non mai (sono sicuro che hai le tue ragioni e stai cacciando le modifiche). Se hai davvero bisogno di cambiare id di volta in volta, ti suggerirei di creare una nuova colonna id fittizia che non sia la chiave primaria / autonumber che puoi gestire tu stesso e generare dai valori correnti. In alternativa, l’idea di Chrisotphers di cui sopra sarebbe il mio altro suggerimento se hai problemi con l’inserimento di id quadro.

In bocca al lupo

PS non ha esito negativo perché l’ordine sequenziale in cui è in esecuzione sta tentando di aggiornare un valore nell’elenco a un elemento già esistente nell’elenco degli id? aggrappandosi a cannucce, forse aggiungere il numero di righe + 1, quindi se funziona sottrarre il numero di righe: -S

Se è necessario modificare occasionalmente gli ID, è probabilmente preferibile non utilizzare una colonna Identity. In passato abbiamo implementato manualmente i campi autonumber usando una tabella ‘Contatori’ che tiene traccia dell’ID successivo per ogni tabella. IIRC lo abbiamo fatto perché le colonne Identity stavano causando il danneggiamento del database in SQL2000 ma essere in grado di cambiare ID era occasionalmente utile per i test.

È ansible inserire nuove righe con valori modificati e quindi eliminare le vecchie righe. L’esempio seguente cambia ID per essere uguale alla chiave esterna PersonId

 SET IDENTITY_INSERT [PersonApiLogin] ON INSERT INTO [PersonApiLogin]( [Id] ,[PersonId] ,[ApiId] ,[Hash] ,[Password] ,[SoftwareKey] ,[LoggedIn] ,[LastAccess]) SELECT [PersonId] ,[PersonId] ,[ApiId] ,[Hash] ,[Password] ,[SoftwareKey] ,[LoggedIn] ,[LastAccess] FROM [db304].[dbo].[PersonApiLogin] GO DELETE FROM [PersonApiLogin] WHERE [PersonId] <> ID GO SET IDENTITY_INSERT [PersonApiLogin] OFF GO 

Prima salva tutti gli ID e alterali a livello di codice ai valori che non vuoi, quindi rimuovili dal database e inseriscili di nuovo usando qualcosa di simile:

 use [Name.Database] go set identity_insert [Test] ON insert into [dbo].[Test] ([Id]) VALUES (2) set identity_insert [Test] OFF 

Per l’uso di inserti di massa:

 use [Name.Database] go set identity_insert [Test] ON BULK INSERT [Test] FROM 'C:\Users\Oscar\file.csv' WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', KEEPIDENTITY) set identity_insert [Test] OFF 

Esempio di dati da file.csv:

 2; 3; 4; 5; 6; 

Se non si imposta identity_insert su off, si otterrà il seguente errore:

Imansible inserire il valore esplicito per la colonna Identity nella tabella ‘Test’ quando IDENTITY_INSERT è impostato su OFF.

Ho visto un buon articolo che mi ha aiutato a uscire all’ultimo momento. Stavo cercando di inserire poche righe in una tabella che aveva la colonna Identity ma l’ho fatta in modo errato e devo cancellare di nuovo. Una volta cancellate le righe, la mia colonna Identity è stata cambiata. Stavo cercando di trovare un modo per aggiornare la colonna che era stata inserita, ma senza fortuna. Quindi, durante la ricerca su google trovato un link ..

  1. Cancellate le colonne che erano state inserite erroneamente
  2. Usa l’inserimento forzato usando l’id quadro on / off (spiegato sotto)

http://beyondrelational.com/modules/2/blogs/28/posts/10337/sql-server-how-do-i-insert-an-explicit-value-into-an-identity-column-how-do- i-update-il-value-of-an.aspx

Una domanda molto bella, in primo luogo abbiamo bisogno di IDENTITY_INSERT per la tabella specifica, dopo di che eseguire la query di inserimento (necessario specificare il nome della colonna).

Nota: dopo aver modificato la colonna Identity, non dimenticare di distriggersre IDENTITY_INSERT. Se non hai fatto, non puoi modificare la colonna Identity per qualsiasi altra tabella.

 SET IDENTITY_INSERT Emp_tb_gb_Menu ON INSERT Emp_tb_gb_Menu(MenuID) VALUES (68) SET IDENTITY_INSERT Emp_tb_gb_Menu OFF 

http://allinworld99.blogspot.com/2016/07/how-to-edit-identity-field-in-sql.html