MiBol MiBol - 26 days ago 8
SQL Question

MS SQL Server (Ver 2008 or above) - How to recreate my old ID column

I have a table in csv file (with an ID as integer).

I manually uploaded the information from the file to a SQL Server data table (creating my ID column as

numeric
).

But, I want to recreate my ID column as autonumeric ID column that continue the number with the latest entry.

Example: the table have the ID 1, 5, 10. I want to recreate the ID column (leaving my old ID's) and next row insertion continue with ID 11.

I suppose that doesn't exists a single method to achieve this. But I want to know the steps that I should follow.

Answer

Here is a script to give you an idea of one way you can do it.

IF OBJECT_ID('DELETEME.dbo.Tbl') IS NOT NULL
    BEGIN
        DROP TABLE Tbl
    END

IF OBJECT_ID('DELETEME.dbo.stageTbl') IS NOT NULL
    BEGIN
        DROP TABLE stageTbl
    END

CREATE TABLE Tbl (
    ID INT
    ,A CHAR(1)
)

INSERT INTO Tbl VALUES (1,'A'),(2,'B'),(10,'C')

SELECT *
FROM
    Tbl

EXEC sp_rename 'DELETEME.dbo.Tbl', 'stageTbl', 'OBJECT'
--renames original table

--create script for the new table
CREATE TABLE Tbl (
    ID INT NOT NULL IDENTITY(1,1)
    ,A CHAR(1)
)


--have to set IDENTITY_INSERT on to insert the ID into an IDENTITY column
SET IDENTITY_INSERT Tbl ON

INSERT INTO Tbl (ID, A)
SELECT ID, A
FROM
    stageTbl

SET IDENTITY_INSERT Tbl OFF

DROP TABLE stageTbl
--drops original table

DBCC CHECKIDENT('Tbl', RESEED, 222)
--sets the number you want to with next if you set as 222 the next identity will be 223

INSERT INTO Tbl (A) VALUES ('D')

SELECT *
FROM
    Tbl

Basic Steps

  • Renames original Table (if you want your new table to be the same name as the old, I like to rename first due to auto generated names of constraints etc on the new table)
  • Create the New table with the Column as an Identity column
  • Turn on IDENTITY_INSERT
  • Select all records from the old table into the new one
  • Turn off IDENTITY_INSERT
  • You don't have to but you can RESSED the identity to start with whatever number you want otherwise SQL-server will automatically do this based on the greatest ID value.
  • Drop the original table that you renamed