Jak programowo zmienić wartości kolumn tożsamości?


160

Mam bazę danych MS SQL 2005 z tabelą Testz kolumną ID. IDto kolumna tożsamości.

Mam wiersze w tej tabeli i wszystkie mają odpowiadające im wartości automatycznie zwiększane o identyfikator.

Teraz chciałbym zmienić każdy identyfikator w tej tabeli w następujący sposób:

ID = ID + 1

Ale kiedy to robię, pojawia się błąd:

Nie można zaktualizować kolumny tożsamości „ID”.

Próbowałem tego:

    ALTER TABLE Test NOCHECK CONSTRAINT ALL 
    set identity_insert ID ON

Ale to nie rozwiązuje problemu.

Muszę ustawić tożsamość w tej kolumnie, ale od czasu do czasu muszę też zmieniać wartości. Więc moje pytanie brzmi, jak wykonać to zadanie.

Odpowiedzi:


220

Musisz

set identity_insert YourTable ON

Następnie usuń wiersz i włóż go ponownie z inną tożsamością.

Po zakończeniu wstawiania nie zapomnij wyłączyć funkcji identity_insert

set identity_insert YourTable OFF

133
ustawienie to będzie działać tylko podczas wstawiania danych, a nie podczas aktualizacji. Instrukcja UPDATE nadal nie powiedzie się.
Husein Roncevic

31
Aby zaktualizować, musisz usunąć i ponownie wstawić. Nie ma innego wyjścia.
popioły 999

1
@MartinSmith Twoje rozwiązanie wydaje się zbyt długie. Możliwość zrobienia tego w dwóch etapach (wyłączenie tożsamości, usunięcie / wstawienie, włączenie tożsamości) jest znacznie bardziej efektywna.
popioły 999

3
@ ashes999 Czyli 4 kroki, a nie 2. Moja odpowiedź jest jeszcze tylko jedna (tworzenie tabeli, przełączanie, aktualizacja, przełączanie z powrotem, upuszczanie). Prawdopodobnie lepiej znasz te kroki, więc wyglądają na mniej zawiłe. Aktualizowanie może być znacznie bardziej wydajne niż usuwanie wstawki, jeśli dotyczy wielu wierszy. Gdzie również trzymasz usunięte wiersze? Jeśli #tempstół, który następnie upuszczasz, jest kolejnym krokiem, którego tutaj nie policzyłeś.
Martin Smith,

40

IDENTITY wartości kolumn są niezmienne.

Istnieje jednak możliwość przełączenia metadanych tabeli w celu usunięcia IDENTITYwłaściwości, wykonania aktualizacji, a następnie przełączenia z powrotem.

Zakładając następującą strukturę

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'

Wtedy możesz to zrobić

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

W SQL Server 2012 można mieć kolumnę automatycznie zwiększającą się, którą można również łatwiej aktualizować za pomocą 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

1
Bardzo zgrabny. Naucz się czegoś nowego każdego dnia (przy okazji testowałem to na SQLExpress; pomimo przełącznika SWITCH TO najwyraźniej nie używa partycjonowania). Zauważ, że tabela musi pasować prawie dokładnie (indeksy, FK itp.)
Mark Sowul

1
Czy metoda przełączania działa, gdy masz PK, SK, indeks i widoki utworzone na podstawie oryginalnej tabeli? Czy złamią się używając tej metody?
tj

1
@tj podczas tworzenia skryptu tabeli tymczasowej z tabeli źródłowej, w tym wszystkich indeksów i ograniczeń. Następnie zmień nazwy tabeli i ograniczeń, aby uniknąć kolizji. Widoki nie będą powodować problemu, chyba że zostaną zindeksowane lub objęte schematem.
Martin Smith

Nie działa, jeśli w tabeli znajduje się indeks wyszukiwania pełnotekstowego. Przypuszczam, że można go usunąć i odtworzyć później, ale nie testowałem.
youen

26

Za pomocą interfejsu użytkownika w menedżerze SQL Server 2005 zmień kolumnę, usuń właściwość autonumer (tożsamość) kolumny (wybierz tabelę, klikając ją prawym przyciskiem myszy i wybierz opcję „Projekt”).

Następnie uruchom zapytanie:

UPDATE table SET Id = Id + 1

Następnie przejdź i dodaj właściwość autonumber z powrotem do kolumny.


3
Jeśli dokonujesz zmiany ręcznie, możesz poprosić kierownika o wygenerowanie skryptu SQL dla zmiany (menu projektanta tabel, wygeneruj skrypt zmiany). W przypadku tej zmiany tworzy nową tabelę i kopiuje dane w poprzek, a następnie usuwa oryginał.
Robin Bennett

2
@tomaszs - Przykład kodu, który jest również bardziej wydajny, ponieważ w ogóle nie odbudowuje fizycznie tabeli (zrobiłoby to dwa razy) jest w mojej późnej odpowiedzi tutaj
Martin Smith

Nie możesz z powrotem dodać tożsamości. Czy ty? stackoverflow.com/questions/1049210/…
Tomas Kubes

Dzięki. To było idealne. Miałem 1 wiersz w mojej tabeli, o którym nie byłem świadomy, więc moje skrypty ładujące dla mojej tabeli miały Idwartości Auto Identity przesunięte o 1.
Shiva

18

Po pierwsze, włączenie lub wyłączenie IDENTITY_INSERT w tym przypadku nie będzie działać dla tego, czego potrzebujesz (jest używane do wstawiania nowych wartości, takich jak podłączanie luk).

Wykonywanie operacji za pośrednictwem graficznego interfejsu użytkownika po prostu tworzy tabelę tymczasową, kopiuje wszystkie dane do nowej tabeli bez pola tożsamości i zmienia nazwę tabeli.


9

Można to zrobić za pomocą tabeli tymczasowej.

Pomysł

  • wyłącz ograniczenia (w przypadku, gdy do twojego identyfikatora odwołuje się klucz obcy)
  • utwórz tabelę tymczasową z nowym identyfikatorem
  • usunąć zawartość tabeli
  • skopiuj dane ze skopiowanej tabeli do oryginalnej tabeli
  • włącz wcześniej wyłączone ograniczenia

Zapytania SQL

Powiedzmy, że twoja testtabela ma dwie dodatkowe kolumny ( column2i column3) oraz że istnieją 2 tabele z odwołaniami do kluczy obcych o testnazwie foreign_table1i foreign_table2(ponieważ rzeczywiste problemy nigdy nie są proste).

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;

Otóż ​​to.


6

DBCC CHECKIDENT ('databasename.dbo.orders', RESEED, 999) możesz zmienić dowolny numer kolumny tożsamości za pomocą tego polecenia, a także możesz rozpocząć ten numer pola od każdego numeru, na przykład w moim poleceniu proszę zacząć od 1000 (999 + 1) mam nadzieję, że wystarczy ... powodzenia


4

Jeśli kolumna nie jest PK, zawsze możesz utworzyć Nową kolumnę w tabeli z rosnącymi liczbami, usunąć oryginał, a następnie zmienić nową, tak aby była stara.

ciekawy, dlaczego możesz to zrobić ... większość, z którymi kiedykolwiek miałem do czynienia z kolumnami tożsamości, polegała na uzupełnianiu liczb, a skończyło się na użyciu DBCC CHECKIDENT (nazwa tabeli, RESEED, newnextnumber)

powodzenia!


1

Modyfikacja tożsamości może się nie powieść w zależności od wielu czynników, głównie związanych z obiektami / relacjami powiązanymi z kolumną id. Wygląda na to, że projekt db jest tutaj tak samo ważny, jak identyfikatory powinny rzadko, jeśli w ogóle, zmieniać (jestem pewien, że masz swoje powody i kaskadowo wprowadzasz zmiany). Jeśli naprawdę potrzebujesz od czasu do czasu zmieniać identyfikatory, sugerowałbym utworzenie nowej fikcyjnej kolumny identyfikatora, która nie jest kluczem podstawowym / automatycznym numerem, którym możesz samodzielnie zarządzać i generować na podstawie bieżących wartości. Alternatywnie, powyższy pomysł Chrisotphersa byłby moją inną sugestią, jeśli masz problemy z zezwoleniem na wstawianie tożsamości.

Powodzenia

PS to nie zawodzi, ponieważ kolejność, w której działa, próbuje zaktualizować wartość na liście do pozycji, która już istnieje na liście identyfikatorów? ściskając słomki, być może dodaj liczbę rzędów + 1, a jeśli to zadziała, odejmij liczbę rzędów: -S


1

Jeśli musisz od czasu do czasu zmieniać identyfikatory, prawdopodobnie najlepiej nie używać kolumny tożsamości. W przeszłości zaimplementowaliśmy ręcznie automatyczne numerowanie pól przy użyciu tabeli „Liczniki”, która śledzi następny identyfikator każdej tabeli. IIRC zrobiliśmy to, ponieważ kolumny tożsamości powodowały uszkodzenie bazy danych w SQL2000, ale możliwość zmiany identyfikatorów była czasami przydatna do testowania.


1

Możesz wstawiać nowe wiersze ze zmodyfikowanymi wartościami, a następnie usuwać stare wiersze. Poniższy przykład zmień identyfikator na taki sam jak klucz obcy 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

1

Najpierw zapisz wszystkie identyfikatory i zmień je programowo na wartości, których chcesz, następnie usuń je z bazy danych, a następnie wstaw ponownie, używając czegoś podobnego:

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

Do wkładek zbiorczych:

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

Przykładowe dane z pliku.csv:

2;
3;
4;
5;
6;

Jeśli nie identity_insertwyłączysz, pojawi się następujący błąd:

Nie można wstawić jawnej wartości dla kolumny tożsamości w tabeli „Test”, gdy parametr IDENTITY_INSERT jest ustawiony na OFF.


0

Widziałem dobry artykuł, który pomógł mi w ostatniej chwili. Próbowałem wstawić kilka wierszy w tabeli, która miała kolumnę tożsamości, ale zrobiłem to źle i musiałem usunąć z powrotem. Po usunięciu wierszy zmieniła się moja kolumna tożsamości. Próbowałem znaleźć sposób na zaktualizowanie wstawionej kolumny, ale - bez powodzenia. Tak więc podczas wyszukiwania w Google znalazłem link ...

  1. Usunięto kolumny, które zostały nieprawidłowo wstawione
  2. Użyj wymuszonego wstawiania za pomocą włączania / wyłączania tożsamości (wyjaśniono poniżej)

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-the-value-of-an.aspx


1
Jednak nie jestem pewien, czy naprawdę odpowiedziałeś na to pytanie.
Andrew Barber,

0

Bardzo ładne pytanie, najpierw musimy na IDENTITY_INSERT dla konkretnej tabeli, a następnie uruchomić zapytanie wstawiające (należy określić nazwę kolumny).

Uwaga: po edycji kolumny tożsamości nie zapomnij wyłączyć IDENTITY_INSERT. Jeśli tego nie zrobiłeś, nie możesz edytować kolumny tożsamości dla żadnej innej tabeli.

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

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.