Rozumiem twoje pytanie: masz istniejącą tabelę z kolumną, która do tej pory była wypełniona wartościami ręcznymi, a teraz chcesz (1) ustawić tę kolumnę jako IDENTITYkolumnę i (2) upewnić się, że IDENTITYpoczątki od najnowszej wartości w istniejących wierszach.
Po pierwsze, niektóre dane testowe do zabawy:
CREATE TABLE dbo.ident_test (
id int NOT NULL,
xyz varchar(10) NOT NULL,
CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id)
);
INSERT INTO dbo.ident_test (id, xyz)
VALUES (1, 'test'),
(2, 'test'),
(5, 'test'),
(6, 'test'),
(10, 'test'),
(18, 'test'),
(19, 'test'),
(20, 'test');
Celem jest, aby kolumny klucza podstawowego tabeli, w idAn IDENTITYkolumna, która rozpocznie się o godzinie 21 do następnego rekordu, który zostanie wstawiony. W tym przykładzie kolumna xyzreprezentuje wszystkie pozostałe kolumny tabeli.
Zanim cokolwiek zrobisz, przeczytaj ostrzeżenia na dole tego postu.
Po pierwsze, na wypadek, gdyby coś poszło nie tak:
BEGIN TRANSACTION;
Teraz dodajmy tymczasową kolumnę roboczą id_tempi ustawmy tę kolumnę na wartości istniejącej idkolumny:
ALTER TABLE dbo.ident_test ADD id_temp int NULL;
UPDATE dbo.ident_test SET id_temp=id;
Następnie musimy usunąć istniejącą idkolumnę (nie możesz po prostu „dodać” IDENTITYdo istniejącej kolumny, musisz utworzyć kolumnę jako IDENTITY). Klucz podstawowy również musi przejść, ponieważ kolumna zależy od niego.
ALTER TABLE dbo.ident_test DROP CONSTRAINT PK_ident_test;
ALTER TABLE dbo.ident_test DROP COLUMN id;
... i dodaj kolumnę ponownie, tym razem jako IDENTITY, wraz z kluczem podstawowym:
ALTER TABLE dbo.ident_test ADD id int IDENTITY(1, 1) NOT NULL;
ALTER TABLE dbo.ident_test ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id);
Oto, gdzie robi się ciekawie. Możesz włączyć IDENTITY_INSERTw tabeli, co oznacza, że możesz ręcznie zdefiniować wartości IDENTITYkolumny podczas wstawiania nowych wierszy (jednak nie aktualizując istniejących wierszy).
SET IDENTITY_INSERT dbo.ident_test ON;
Przy takim zestawie DELETEwszystkie wiersze w tabeli, ale wiersze, które usuwasz, znajdują się OUTPUTbezpośrednio w tej samej tabeli - ale z określonymi wartościami dla idkolumny (z kolumny zapasowej).
DELETE FROM dbo.ident_test
OUTPUT deleted.id_temp AS id, deleted.xyz
INTO dbo.ident_test (id, xyz);
Po zakończeniu IDENTITY_INSERTwyłącz ponownie.
SET IDENTITY_INSERT dbo.ident_test OFF;
Usuń tymczasową kolumnę, którą dodaliśmy:
ALTER TABLE dbo.ident_test DROP COLUMN id_temp;
I w końcu ponownie umieściłem IDENTITYkolumnę, więc następny rekord idzostanie wznowiony po najwyższej istniejącej liczbie w idkolumnie:
DECLARE @maxid int;
SELECT @maxid=MAX(id) FROM dbo.ident_test;
DBCC CHECKIDENT ("dbo.ident_test", RESEED, @maxid)
Sprawdzając przykładową tabelę, najwyższa idliczba to 20.
SELECT * FROM dbo.ident_test;
Dodaj kolejny wiersz i sprawdź jego nowy IDENTITY:
INSERT INTO dbo.ident_test (xyz) VALUES ('New row');
SELECT * FROM dbo.ident_test;
W tym przykładzie nowy wiersz będzie miał id=21. Wreszcie, jeśli jesteś zadowolony, dokonaj transakcji:
COMMIT TRANSACTION;
Ważny
Nie jest to trywialna operacja i niesie ze sobą całkiem sporo zagrożeń, o których powinieneś wiedzieć.
Zrób to w dedykowanym środowisku testowym. Miej kopie zapasowe. :)
Lubię go używać, BEGIN/COMMIT TRANSACTIONponieważ zapobiega bałaganowi innych procesów w tabeli, gdy jesteś w trakcie jej zmieniania, i daje ci możliwość przywrócenia wszystkiego do tyłu, jeśli coś pójdzie nie tak. Jednak każdy inny proces, który próbuje uzyskać dostęp do tabeli przed zatwierdzeniem transakcji, czeka. Może to być dość złe, jeśli masz duży stół i / lub pracujesz w środowisku produkcyjnym.
OUTPUT .. INTOnie będzie działać, jeśli tabela docelowa ma ograniczenia dla klucza obcego lub dowolną z wielu innych funkcji, których nie pamiętam z głowy. Zamiast tego możesz zamiast tego rozładować dane do tabeli tymczasowej, a następnie wstawić je z powrotem do oryginalnej tabeli. Możesz być w stanie używać przełączania partycji (nawet jeśli nie używasz partycji).
Uruchom te instrukcje jeden po drugim, a nie jako partia lub procedura składowana.
Spróbuj wymyślić inne rzeczy, które mogą zależeć od idupuszczanej i odtwarzanej kolumny. Wszelkie indeksy będą musiały zostać usunięte i ponownie utworzone (tak jak zrobiliśmy to z kluczem podstawowym). Pamiętaj, aby wykonać skrypt każdego indeksu i ograniczenia, które trzeba będzie wcześniej utworzyć.
Wyłącz dowolne INSERTi DELETEwyzwalacze na stole.
Jeśli ponowne utworzenie tabeli jest opcją:
Jeśli ponowne utworzenie tabeli jest dla Ciebie opcją, wszystko jest o wiele prostsze:
- Tworzenie pustego stołu, z
idkolumny jako IDENTITY,
- Zestaw
IDENTITY_INSERT ONdo stołu,
- Wypełnij stół,
- Ustaw
IDENTITY_INSERT OFFi
- Ponownie sprawdź tożsamość.
IDENTITY?