1

After deleting the duplicate records from the table, I want to update Identity column of a table with consecutive numbering starting with 1. Here is my table details 

id(identity(1,1)),
EmployeeID(int),
Punch_Time(datetime),
Deviceid(int)

enter image description here

I need to perform this action through a stored procedure. When i tried following statement in stored procedure

DECLARE @myVar int
        SET @myVar = 0
        set identity_insert TempTrans_Raw# ON
        UPDATE  TempTrans_Raw# SET  @myvar = Id = @myVar + 1
        set identity_insert TempTrans_Raw# off

gave error like...Cannot update identity column 'Id' Anyone please suggest how to update Identity column of that table with consecutive numbering starting with 1.

Robert
  • 25,425
  • 8
  • 67
  • 81
AcAnanth
  • 765
  • 3
  • 19
  • 53
  • 1
    identity was never meant to be used as a serial number and continuity is never guaranteed. If you really need a serial number, try adding another column and populating it – Raj Apr 23 '15 at 09:55
  • @Raj : other than a new coulmn is there any other solution? – AcAnanth Apr 23 '15 at 09:58
  • you can drop and recreate the column, but if you don't care for the content, why do you need that column ? – A ツ Apr 23 '15 at 10:00
  • http://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008 – ps_prakash02 Apr 23 '15 at 10:00
  • your update statement like below SET IDENTITY_INSERT tablename ON UPDATE STATEMENT SET IDENTITY_INSERT tablename OFF – ps_prakash02 Apr 23 '15 at 10:01
  • i am getting duplicate records in that table so after deleting identity column is like this way – AcAnanth Apr 23 '15 at 10:02
  • i already tried SET IDENTITY_INSERT on then update then SET IDENTITY_INSERT off .... – AcAnanth Apr 23 '15 at 10:03
  • Why oh why oh why? Is that column a primary key? I hope not or your database model is taking Tyson style hits to the face. – John Bell Apr 23 '15 at 10:03
  • 1. Create a new column and update it with serial number 2. Drop the existing column 3. Rename the new column with existing column name – ps_prakash02 Apr 23 '15 at 10:05

4 Answers4

4
--before running this make sure Foreign key constraints have been removed that reference the ID. 

--insert everything into a temp table
SELECT (ColumnList) --except identity column
INTO #tmpYourTable
FROM yourTable

--clear your table
DELETE FROM yourTable
-- reseed identity
DBCC CHECKIDENT('table', RESEED, new reseed value)
--insert back all the values 
INSERT INTO yourTable (ColumnList)
SELECT OtherCols FROM #tmpYourTable
--drop the temp table
DROP TABLE #tmpYourTable
GO
Raj
  • 10,653
  • 2
  • 45
  • 52
1

If you really need sequential numbers, may I suggest that you create a table ("OrderNumbers") with valid numbers, and then make you program pick one row from OrderNumbers when you add a row to yourTable.

If you everything in one transaction (i.e. with Begin Tran and Commit) then you can get one number for one row with no gabs.

You should have either Primary Keys or Unique Keys on both tables on this column to protect against duplicates.

HIH, Henrik

Henrik Staun Poulsen
  • 13,154
  • 4
  • 23
  • 26
1

The IDENTITY keword is used to generate a key which can be used in combination with the PRIMARY KEY constraint to get a technical key. Such keys are technical, they are used to link table records. They should have no other meaning (such as a sort order). SQL Server does not guarantee the generated IDs to be consecutive. They do guarantee however that you get them in order. (So you might get 1, 2, 4, ..., but never 1, 4, 2, ...)

Here is the documentation for IDENTITY: https://msdn.microsoft.com/de-de/library/ms186775.aspx.

Personally I don't like it to be guaranteed that the generated IDs are in order. A technical ID is supposed to have no meaning other then offering a reference to a record. You can rely on the order, but if order is information you are interested in, you should store that information in my opinion (in form of a timestamp for example).

If you want to have a number telling you that a record is the fifth or sixteenth or whatever record in order, you can get always get that number on the fly using the ROW_NUMBER function. So there is no need to generate and store such consecutive value (which could also be quite troublesome when it comes to concurrent transactions on the table). Here is how to get that number:

select
  row_number() over(order by id),
  employeeid,
  punch_time,
  deviceid
from mytable;

Having said all this; it should never be necessary to change an ID. It is a sign for inappropriate table design, if you feel that need.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
-1

Check this function: DBCC CHECKIDENT('table', RESEED, new reseed value)

Thanos Markou
  • 2,587
  • 3
  • 25
  • 32