Een identiteit toevoegen aan een bestaande kolom

Ik moet de primaire sleutel van een tabel wijzigen in een identiteitskolom en er zijn al een aantal rijen in de tabel.

Ik heb een script om de ID’s op te schonen om ervoor te zorgen dat ze opeenvolgend zijn vanaf 1 en werkt prima in mijn testdatabase.

Wat is het SQL-commando om de kolom te wijzigen zodat deze een identiteitseigenschap heeft?


Antwoord 1, autoriteit 100%

U kunt de bestaande kolommen voor identiteit niet wijzigen.

Je hebt 2 opties,

  1. Maak een nieuwe tabel met identiteit & laat de bestaande tafel vallen

  2. Maak een nieuwe kolom met identiteit & laat de bestaande kolom vallen

Benadering 1. (Nieuwe tabel) Hier kunt u de bestaande gegevenswaarden in de nieuw gemaakte identiteitskolom behouden. Houd er rekening mee dat u alle gegevens verliest als niet wordt voldaan aan ‘indien niet bestaat’, dus zorg ervoor dat u de voorwaarde ook op de drop zet!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_Names ON
go
IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
DROP TABLE dbo.Names
go
Exec sp_rename 'Tmp_Names', 'Names'

Benadering 2 (Nieuwe kolom) U kunt de bestaande gegevenswaarden in de nieuw gemaakte identiteitskolom niet behouden. De identiteitskolom bevat de nummerreeks.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go
Alter Table Names Drop Column ID
Go
Exec sp_rename 'Names.Id_new', 'ID', 'Column'

Zie het volgende Microsoft SQL Server-forumbericht voor meer details:

Kolom wijzigen naar identiteit(1,1)


Antwoord 2, autoriteit 44%

In SQL 2005 en hoger is er een truc om dit probleem op te lossen zonder de gegevenspagina’s van de tabel te wijzigen. Dit is belangrijk voor grote tabellen waar het aanraken van elke gegevenspagina minuten of uren kan duren. De truc werkt ook als de identiteitskolom een primaire sleutel is, deel uitmaakt van een geclusterde of niet-geclusterde index, of andere valkuilen die de eenvoudigere “kolom toevoegen/verwijderen/hernoemen”-oplossing kunnen verstoren.

Dit is de truc: u kunt de ALTER TABLE…SWITCH-instructie om het schema van een tabel te wijzigen zonder de gegevens te wijzigen, wat betekent dat u een tabel met een IDENTITY kunt vervangen door een identiek tabelschema, maar zonder een IDENTITY-kolom. Dezelfde truc werkt om IDENTITEIT aan een bestaande kolom toe te voegen.

Normaal gesproken, WIJZIG TABEL.. .SWITCHwordt gebruikt om een volledige partitie in een gepartitioneerde tabel efficiënt te vervangen door een nieuwe, lege partitie. Maar het kan ook worden gebruikt in niet-gepartitioneerde tabellen.

Ik heb deze truc gebruikt om in minder dan 5 seconden een kolom van een tabel met 2,5 miljard rijen te converteren van IDENTITY naar een non-IDENTITY (om een query van meerdere uren uit te voeren waarvan het queryplan beter werkte voor niet-IDENTITY-kolommen) en herstelde vervolgens de IDENTITY-instelling, opnieuw in minder dan 5 seconden.

Hier is een codevoorbeeld van hoe het werkt.

CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );
 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');
 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );
 ALTER TABLE Test SWITCH TO Test2;
 -- drop the original (now empty) table
 DROP TABLE Test;
 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';
 -- update the identity seed
 DBCC CHECKIDENT('Test');
 -- see same records
 SELECT * FROM Test; 

Dit is duidelijk meer betrokken dan de oplossingen in andere antwoorden, maar als uw tafel groot is, kan dit een echte reddingsboei zijn. Er zijn enkele kanttekeningen:

  • Voor zover ik weet, is identiteit het enige dat je met deze methode aan de kolommen van je tabel kunt veranderen. Het toevoegen/verwijderen van kolommen, het wijzigen van nullability, etc. is niet toegestaan.
  • Je moet vreemde sleutels laten vallen voordat je overschakelt en ze daarna herstellen.
  • Hetzelfde voor WITH SCHEMABINDING-functies, weergaven, enz.
  • De indexen van de nieuwe tabel moeten exact overeenkomen (dezelfde kolommen, dezelfde volgorde, enz.)
  • Oude en nieuwe tabellen moeten in dezelfde bestandsgroep staan.
  • Werkt alleen op SQL Server 2005 of later
  • Ik geloofde eerder dat deze truc alleen werkt op de Enterprise- of Developer-edities van SQL Server (omdat partities alleen worden ondersteund in Enterprise- en Developer-versies), maar Mason G. Zhwiti zegt in zijn onderstaande opmerking dat het ook werkt in SQL Standard Editie ook. Ik neem aan dat dit betekent dat de beperking tot Enterprise of Developer niet van toepassing is op ALTER TABLE…SWITCH.

Er is een goed artikel over TechNetwaarin de bovenstaande vereisten worden beschreven.

UPDATE – Eric Wuhad hieronder een opmerking met belangrijke informatie over deze oplossing. Kopieer het hier om ervoor te zorgen dat het meer aandacht krijgt:

Er is nog een waarschuwing die het vermelden waard is. Hoewel de
nieuwe tabel ontvangt graag gegevens van de oude tabel, en alle
nieuwe rijen worden ingevoegd volgens een identiteitspatroon, ze zullen
beginnen bij 1 en mogelijk breken als de genoemde kolom een primaire sleutel is.
Overweeg om DBCC CHECKIDENT('<newTableName>')direct daarna uit te voeren
schakelen. Zie msdn.microsoft.com/en-us/library/ms176057.aspxvoor meer
info.

Als de tabel actief wordt uitgebreid met nieuwe rijen (wat betekent dat u niet veel of geen downtime heeft tussen het toevoegen van IDENTITY en het toevoegen van nieuwe rijen, dan wilt u in plaats van DBCC CHECKIDENThandmatig stel de identiteits-seedwaarde in het nieuwe tabelschema in op groter dan de grootste bestaande ID in de tabel, bijvoorbeeld IDENTITY (2435457, 1). Mogelijk kunt u zowel de ALTER TABLE...SWITCHen de DBCC CHECKIDENTin een transactie (of niet – heb dit niet getest), maar het lijkt erop dat het handmatig instellen van de seed-waarde gemakkelijker en veiliger zal zijn.

Het is duidelijk dat als er geen nieuwe rijen aan de tabel worden toegevoegd (of ze worden slechts af en toe toegevoegd, zoals een dagelijks ETL-proces), deze raceconditie niet zal plaatsvinden, dus DBCC CHECKIDENTis in orde .


Antwoord 3, autoriteit 16%

U kunt een kolom niet wijzigen in een IDENTITEIT-kolom. Wat u moet doen, is een nieuwe kolom maken die vanaf het begin is gedefinieerd als een IDENTITEIT, vervolgens de oude kolom laten vallen en de nieuwe naam wijzigen in de oude naam.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)
ALTER TABLE (yourTable) DROP COLUMN OldColumnName
EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc


Antwoord 4, autoriteit 3%

Er is een coole oplossing die hier wordt beschreven:
SQL SERVER – Toevoegen of verwijder identiteitseigenschap op kolom

Kortom, bewerk handmatig uw tabel in SQL Manager, verander de identiteit, BEWAAR GEEN wijzigingen, toon gewoon het script dat voor de wijzigingen wordt gemaakt, kopieer het en gebruik het later.

Het bespaart enorm veel tijd, omdat het (het script) alle externe sleutels, indices, enz. bevat die verband houden met de tabel die u wijzigt. Dit handmatig schrijven… God verhoede.


Antwoord 5, autoriteit 3%

Overweeg om SEQUENCE te gebruiken in plaats van IDENTITY.

IN sql server 2014 (ik weet niet hoe het met lagere versies zit) kun je dit eenvoudig doen, met behulp van sequence.

CREATE SEQUENCE  sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;
ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name

Vanaf hier: Sequence als standaardwaarde voor een kolom


Antwoord 6

Eenvoudige uitleg

Hernoem de bestaande kolom met sp_RENAME

EXEC sp_RENAME ‘Table_Name.Existing_ColumnName’ , ‘New_ColumnName’, ‘COLUMN’

Voorbeeld voor hernoemen:

De bestaande kolom UserID wordt hernoemd naar OldUserID

EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'

Voeg vervolgens een nieuwe kolom toe met behulp van de wijzigingsquery om deze in te stellen als primaire sleutel en identiteitswaarde

ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Voorbeeld voor primaire sleutel instellen

De nieuw gemaakte kolomnaam is UserID

ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Laat vervolgens de hernoemde kolom vallen

ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName

Voorbeeld van hernoemde kolom Drop

ALTER TABLE Users DROP COLUMN OldUserID

Nu hebben we een primaire sleutel en identiteit toegevoegd aan de bestaande kolom in de tabel.


Antwoord 7

Ik ben een Java-ontwikkelaar die toevallig in een team kwam zonder DBA en waar ik als ontwikkelaar geen DBA-rechten kan krijgen. Ik kreeg de taak om een volledig schema tussen twee databases te verplaatsen, dus zonder een DBA moest ik het doen en het doen door scripts uit te voeren, omdat ik de GUI in SQL Server 2008 niet kon gebruiken omdat ik geen beheerdersrechten had.

Alles werd zonder problemen verplaatst, maar toen ik een opgeslagen procedure op de nieuwe schema.table uitvoerde, ontdekte ik dat ik het identiteitsveld in een tabel kwijt was. Ik heb het script dat de tabel heeft gemaakt dubbel gecontroleerd en het was er, maar SQL Server kreeg het niet toen ik het script uitvoerde. Later kreeg ik van een DBA te horen dat hij hetzelfde probleem eerder had gezien.

In ieder geval, voor SQL Server 2008 zijn dit de stappen die ik heb genomen om dit op te lossen en ze hebben gewerkt, dus ik post dit hier in de hoop dat het iemand kan helpen. Dit is wat ik deed omdat ik FK-afhankelijkheden had op een andere tafel die dit moeilijker maakten:

Ik heb deze zoekopdracht gebruikt om te verifiëren dat de identiteit inderdaad ontbrak en om afhankelijkheden in de tabel te bekijken.

1.) Statistieken op een tafel zoeken:

exec sp_help 'dbo.table_name_old';

2.) Maak een duplicaat, identieke nieuwe tabel, maar voeg een identiteitsveld toe aan het PK-veld waar het eerder was.

3.) Schakel de identiteit uit om gegevens te verplaatsen.

SET IDENTITY_INSERT dbo.table_name ON 

4.) Breng de gegevens over.

INSERT INTO dbo.table_name_new
(
field1, field2, etc...
)
SELECT 
field1, field2, etc...
FROM 
dbo.table_name_old;

5.) Controleer of de gegevens aanwezig zijn.

SELECT * FROM dbo.table_name_new

6.) Schakel de identiteit opnieuw in.

SET IDENTITY_INSERT ToyRecP.ToyAwards.lkpFile_New OFF

7.) Dit is het beste script dat ik heb gevonden om alle FK-relaties op te halen om te verifiëren naar welke tabel(len) de originele tabel verwijst als afhankelijkheden
en ik kwam er veel tegen, dus het is een blijvertje!

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
   ORDER BY ReferenceTableName;

8.) Zorg ervoor dat u alle PK- en FK-scripts voor alle betrokken tabellen hebt, voor deze volgende stap.

9.) U kunt met de rechtermuisknop op elke toets klikken en dit scripten met SQL Server 2008

10.) Verwijder de FK(‘s) uit de afhankelijkheidstabel(len) met deze syntaxis:

ALTER TABLE [dbo].[table_name] DROP CONSTRAINT [Name_of_FK]

11.) Zet de originele tabel neer:

DROP TABLE dbo.table_name_old;

13.) Deze volgende stappen zijn afhankelijk van de scripts die u in stap 9 in SQL Server 2008 hebt gemaakt.

–Voeg de PK toe aan de nieuwe tabel.

–Voeg de FK toe aan de nieuwe tafel.

–Voeg de FK’s weer toe aan de afhankelijkheidstabel.

14.) Controleer of alles correct en volledig is. Ik heb de GUI gebruikt om naar de tabellen te kijken.

15.) Hernoem de nieuwe tabel naar de originele tafelnaam.

exec sp_RENAME '[Schema_Name.OldTableName]' , '[NewTableName]';

Eindelijk werkte alles!


Antwoord 8

je kunt het niet zo doen, je moet een andere kolom toevoegen, de originele kolom verwijderen en de nieuwe kolom hernoemen of of een nieuwe tabel maken, de gegevens kopiëren en de oude tabel neerzetten, gevolgd door de nieuwe tabel te hernoemen naar de oude tafel

als u SSMS gebruikt en de identiteitseigenschap in de ontwerper op ON zet, doet SQL Server hier achter de schermen. Dus als je een tabel hebt met de naam [gebruiker], dan gebeurt dit als je UserID en identiteit maakt

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL
    )  ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO
GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]
GO
COMMIT

Dat gezegd hebbende, er is een manier om de systeemtabel te hacken om dit te bereiken door de bitsgewijze waarde in te stellen, maar dat wordt niet ondersteund en ik zou het niet doen


Antwoord 9

Zoals ik heb begrepen in normale gevallen, maken we een tabel met Primaire sleuteldie Identity-eigenschap
heeft
Dus Hernoemenof Verwijdereneen kolom die is gekoppeld aan Primaire sleutelbeperkingis niet mogelijk omdat de beperkingsregels valideren kolomstructuur.
Om dit te bereiken moeten we een aantal stappen op de volgende manier verwerken:
Laten we aannemen dat TableName = ‘Employee’en ColumnName = ‘EmployeeId’

1. Voeg nieuwe kolom ‘EmployeeId_new’ toe aan de tabel ‘Employee’

ALTER TABLE Medewerker ADD EmployeeId_new INT IDENTITY(1,1)

  1. Verwijder nu kolom ‘EmployeeId’ uit de tabel ‘Employee’

    WIJZIG TABEL Medewerker DROP COLUMN EmployeeId

  2. Dit geeft een fout omdat regels voor primaire sleutelbeperking van toepassing zijn en de kolomstructuur valideren.

    *###
    Msg 5074, Level 16, State 1, Line 1 Het object [PK_dbo.Employee] is afhankelijk van colmn [EmployeeId].’###

  3. We moeten dus eerst de beperking van de primaire sleutel uit de tabel ‘Werknemer’ verwijderen, daarna kunnen we de kolom verwijderen

    WIJZIG TABEL Werknemer DROP-beperking [PK_dbo.Employee]

  4. Nu kunnen we de kolom ‘EmployeeId’ uit de tabel ‘Employee’ verwijderen zoals in de vorige stap waar we een fout kregen

    WIJZIG TABEL Medewerker DROP COLUMN EmployeeId

  5. Kolom ‘EmployeeId’ nu uit tabel verwijderd
    Dus we zullen de nieuw toegevoegde nieuwe kolom ‘EmployeeId_new’ hernoemen met ‘EmployeeId’

    sp_rename ‘Employee.EmployeeId’, ‘EmployeeId_new’, ‘COLUMN’

  6. Om de tabel in dezelfde vorm te herschikken als hij was, moeten we de primaire sleutelbeperking toevoegen voor de kolom ‘EmployeeId’

    WIJZIG TABEL Medewerker toevoegen beperking [PK_dbo.Employee] primaire sleutel (EmployeeId)

8. Nu is de tabel ‘Employee’ met ‘EmployeeId’ aangepast voor identiteitsregels samen met de bestaande primaire sleutelbeperking


Antwoord 10

Door het ontwerp is er geen eenvoudige manier om de identiteitsfunctie voor een bestaande kolom in of uit te schakelen. De enige schone manier om dit te doen, is door een nieuwe kolom te maken en er een identiteitskolom van te maken of door een nieuwe tabel te maken en uw gegevens te migreren.

Als we SQL Server Management Studio gebruiken om de identiteitswaarde in kolom “id” te verwijderen, wordt er een nieuwe tijdelijke tabel gemaakt, worden de gegevens verplaatst naar de tijdelijke tabel, wordt de oude tabel verwijderd en wordt de nieuwe tabel hernoemd .

Gebruik Management Studio om de wijziging aan te brengen en klik vervolgens met de rechtermuisknop in de ontwerper en selecteer “Wijzigingsscript genereren”.

Je zult zien dat dit is wat de SQL-server op de achtergrond doet.


Antwoord 11

Als u Visual Studio 2017+ gebruikt

  1. Klik in Server Object Explorer met de rechtermuisknop op uw tabel en selecteer “view code”
  2. Voeg de modifier “IDENTITY” toe aan je kolom
  3. Bijwerken

Dit doet het allemaal voor je.


Antwoord 12

Er is er helaas geen; de eigenschap IDENTITY hoort bij de tabel in plaats van bij de kolom.

De gemakkelijkere manier is om het in de GUI te doen, maar als dit geen optie is, kunt u een heel eind gaan door de gegevens te kopiëren, de kolom te laten vallen, deze opnieuw toe te voegen met identiteit en de gegevens te plaatsen terug.

Zie hiervoor een blow-by-blow-account.


Antwoord 13

Klik met de rechtermuisknop op de tabelnaam in Objectverkenner. Je krijgt een aantal opties. Klik op ‘Ontwerp’. Er wordt een nieuw tabblad geopend voor deze tabel. U kunt hier een identiteitsbeperking toevoegen in ‘Kolomeigenschappen’.


Antwoord 14

Om de identiteitseigenschappen voor een kolom te wijzigen:

  • Klik in Server Explorer met de rechtermuisknop op de tabel met identiteitseigenschappen die u wilt wijzigen en klik op Tabeldefinitie openen.
    De tabel wordt geopend in Table Designer.
  • Wis het selectievakje Nulls toestaan voor de kolom die u wilt wijzigen.
  • Klap op het tabblad Kolomeigenschappen de eigenschap Identiteitsspecificatie uit.
  • Klik op de rastercel voor de onderliggende eigenschap Is identiteit en kies Ja in de vervolgkeuzelijst.
  • Typ een waarde in de cel Identity Seed. Deze waarde wordt toegewezen aan de eerste rij in de tabel. De waarde 1 wordt standaard toegewezen.

Dat was het, en het werkte voor mij


Antwoord 15

Ik geloof niet dat je een bestaande kolom kunt wijzigen in een identiteitskolom met tsql. U kunt dit echter doen via de ontwerpweergave van Enterprise Manager.

U kunt ook een nieuwe rij maken als identiteitskolom, de oude kolom verwijderen en de naam van uw nieuwe kolom wijzigen.

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

Antwoord 16

Als de originele poster eigenlijk een bestaande kolom wilde instellen als PRIMARY KEYvoor de tabel en de kolom eigenlijk niet nodig had als een IDENTITYkolom ( twee verschillende dingen) dan kan dit via t-SQL met:

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

Let op de haakjes rond de kolomnaam na de optie PRIMARY KEY.

Hoewel dit bericht oud is en ik een aanname doe over de behoeften van de aanvragers, vond ik dat deze aanvullende informatie nuttig zou kunnen zijn voor gebruikers die deze thread tegenkomen, omdat ik denk dat het gesprek ertoe kan leiden dat iemand denkt dat een bestaande kolom niet kan worden ingesteld om een primaire sleutel te zijn zonder deze eerst als een nieuwe kolom toe te voegen, wat onjuist zou zijn.


Antwoord 17

Volgens mijn huidige toestand volg ik deze aanpak. Ik wil identiteit geven aan een primaire tabel nadat gegevens zijn ingevoegd via script.

Omdat ik identiteit wil toevoegen, begint het altijd van 1 tot Einde van het aantal records dat ik wil.

--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID 
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)
--then add primary key to that column (exist option you can ignore)
IF  NOT EXISTS (SELECT * FROM sys.key_constraints  WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
    ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO

Hiermee wordt dezelfde primaire sleutelkolom met identiteit gemaakt

Ik heb deze links gebruikt: https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table /

Primaire sleutel toevoegen aan bestaande tabel


Antwoord 18

In principe zijn er vier logische stappen.

  1. Maak een nieuwe identiteitskolom. Schakel Identiteit invoegen in voor deze nieuwe kolom.

  2. Voeg de gegevens uit de bronkolom (de kolom die u naar Identiteit wilt converteren) in deze nieuwe kolom in.

  3. Schakel Identiteit invoegen uit voor de nieuwe kolom.

  4. Laat je bronkolom vallen & hernoem de nieuwe kolom naar de naam van de bronkolom.

Er kunnen wat meer complexiteiten zijn, zoals werken op meerdere servers, enz.

Raadpleeg het volgende artikel voor de stappen (met ssms & T-sql). Deze stappen zijn bedoeld voor beginners met minder grip op T-SQL.

http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms- sql-server.aspx


Antwoord 19

genereert een script voor alle tabellen met primaire sleutel = bigint die geen identiteitsset hebben; dit retourneert een lijst met gegenereerde scripts bij elke tabel;

SET NOCOUNT ON;
declare @sql table(s varchar(max), id int identity)
DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);
DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 from sys.indexes as [i] 
    inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM @sql
declare @pkname varchar(100),
    @pkcol nvarchar(100)
SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name
declare @q nvarchar(max) = 'SELECT  '[email protected]+' FROM ['[email protected]_schema+'].['[email protected]_name+'] ORDER BY '[email protected]+' DESC'
DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT
insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')
-- create statement
insert into  @sql(s) values ('create table ['[email protected]_schema+'].[' + @table_name + '_Temp] (')
-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 
    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)
     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id
 update @sql set s=left(s,len(s)-1) where [email protected]@identity
-- closing bracket
insert into @sql(s) values( ')' )
insert into @sql(s) values( 'SET IDENTITY_INSERT ['[email protected]_schema+'].['[email protected]_name+'_Temp] ON')
declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')
insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['[email protected]_schema+'].['[email protected]_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['[email protected]_schema+'].['[email protected]_name+'_Temp] ('[email protected]+')')
insert into @sql(s) values( 'SELECT '[email protected]+' FROM ['[email protected]_schema+'].['[email protected]_name+']'')')
insert into @sql(s) values( 'SET IDENTITY_INSERT ['[email protected]_schema+'].['[email protected]_name+'_Temp] OFF')
insert into @sql(s) values( 'DROP TABLE ['[email protected]_schema+'].['[email protected]_name+']')
insert into @sql(s) values( 'EXECUTE sp_rename N''['[email protected]_schema+'].['[email protected]_name+'_Temp]'', N'''[email protected]_name+''', ''OBJECT''')
if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['[email protected]_schema+'].['[email protected]_name+'] ADD CONSTRAINT ['[email protected]+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where [email protected]@identity
    insert into @sql(s) values ('  )')
end
insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')
declare @fqry nvarchar(max)
-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))
SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '[email protected]_name
EXEC sp_executeSql @fqry
    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Other episodes