how to update an identity column values

Question

ID  EmployeeName    Address PhoneNo EmailID DOB DOJ Designation Department  CraetedDate
2   Sabarish    Saidapet    9600387983  sabari@gmail.com    -1973   -2012   Ase Software    2013-05-15 17:07:43.223
10  Karthik Chrompet    9865028330  karthik@gmail.com   -1968   -2008   SoftwareEngineeer   Software    2013-05-15 15:40:41.613
15  Sabarish    Saidapet    9600387983  sabari@gmail.com    -1973   -2012   Ase Software    2013-05-15 17:07:12.003

In the above table the id column is an identity column and I want my id values as 1,2,3, instead of 2,10,15 how to do this?

http://stackoverflow.com/questions/751522/how-to-change-identity-column-values-programmatically — lex87, May 15 '13 at 11:56
You can't update directly, you need to delete and then reinsert with new value row — Justin, May 15 '13 at 11:59
ok, without editing table: identity column, is impossible directly Update identity column. — Justin, May 15 '13 at 12:35
@justin duplicate is not an issue and how to update an the identity column as in my required format . as id =1,2,3 — user1500707, May 16 '13 at 05:19
@user1500707 look in first comment there are everything written. you need to turn off identity on ID column, update and again turn on identity column — Justin, May 16 '13 at 05:26

score 1 · Answer 1 · answered May 16 '13 at 13:48

Try this one -

Query:

IF OBJECT_ID (N'dbo.test') IS NOT NULL
   DROP TABLE dbo.test

CREATE TABLE dbo.test
(
      ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , EmployeeName VARCHAR(50) NOT NULL
)

SET IDENTITY_INSERT dbo.test ON

INSERT INTO dbo.test 
(
      ID
    , EmployeeName
)
VALUES 
    (2, 'Sabarish Saidapet'), 
    (10, 'Karthik Chrompet'), 
    (15, 'Sabarish Saidapet')

SET IDENTITY_INSERT dbo.test OFF

SET IDENTITY_INSERT dbo.test ON

DECLARE @temp TABLE
(
        ID INT NOT NULL
      , EmployeeName VARCHAR(50) NOT NULL
)

INSERT INTO @temp (EmployeeName, ID)
SELECT 
      t.EmployeeName 
    , ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM dbo.test t

TRUNCATE TABLE dbo.test
--DELETE FROM dbo.test

INSERT INTO dbo.test (ID, EmployeeName)
SELECT ID, EmployeeName FROM @temp

SET IDENTITY_INSERT dbo.test OFF

INSERT INTO dbo.test (EmployeeName)
VALUES ('test 4')

SELECT * 
FROM dbo.test t

Output:

ID          EmployeeName
----------- --------------------------------------------------
1           Sabarish Saidapet
2           Karthik Chrompet
3           Sabarish Saidapet
4           test 4

how to update an identity column values

1 Answers1