CODEWITHSUNDEEP
sqlsql-serversql-server-2012
noa-dev
noa-dev

Reputation: 3641

MS SQL update column with auto incremented value

i recently imported some old data to a new SQL table and forgot to set the ID column to auto increment.

Now i have several "NULL"s in the column named ID.

Is there a way I can update all ID columns with an unique ID automatically? sadly i have no clue how

After that I will be able to set the column to INT and Auto Increment.

Thank you for your help.

Upvotes: 7

Views: 26377

Answers (5)

Stepan Novikov
Stepan Novikov

Reputation: 1396

Without using of sequences and manual setting of starting value:

declare @MaxId int 

select @MaxId = max(id) from [TableA]

update [TableA] 
  set  @MaxId = id = @MaxId +1
where id is null

Works fine with all versions of SQL Server starting from 2008. Sqlfiddle

Upvotes: 2

Maurits van Beusekom
Maurits van Beusekom

Reputation: 5989

If the current ID column is not important and the value assigned to the ID column is not relevant you could try the following:

  1. Create a new int identity(1,1) column (this will automatically be populated with unique values by MS SQLServer)
  2. Drop the old column
  3. Optionally rename the newly created column to the old name.

A sample script could look like this:

// Create a new identity column    
ALTER TABLE dbo.YourTable 
      ADD NewID INT IDENTITY(1,1);
    GO

// Drop the old ID column
    ALTER TABLE dbo.YourTable
      DROP COLUMN OldID;
    GO

// Rename the new column from 'NewID' to 'OldID'
    EXEC sp_rename 'YourDataBase.YourTable.NewID', 'OldID', 'COLUMN'; 
    GO

This will not work when you have tables that have a relationship based on the values in the old ID column.

Upvotes: 1

user4622594
user4622594

Reputation:

You can do it using a CTE (common table expression):

;WITH cte AS
(
    SELECT  ROW_NUMBER() OVER (ORDER BY ColA) AS RN,
            ID,
            ColA
    FROM    dbo.YourTable
)

UPDATE  cte 
SET     ID = RN

in this example the new id gets generated by ordering by ColA - but you can order by any column(s) you want.

Upvotes: 2

Dhaval
Dhaval

Reputation: 2379

Try using Sequence Object for Sql Server 2012 

create Sequence Sq as int
minvalue 1
cycle;

update table set Column=NEXT VALUE FOR Sq where Column is null

Upvotes: 11

Sauget Charles-Henri
Sauget Charles-Henri

Reputation: 461

The easiest way is to remove your old ID Column and add a new column ID with : 

  ALTER TABLE [dbo].[myTable] DROP COLUMN ID 
  ALTER TABLE [dbo].[myTable] ADD ID int IDENTITY

Upvotes: 5

Related Questions