Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to Reset Identity Column Value In SQL Server without Delete Records.

, 17 Dec 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
You have deleted some records from different record position from your identity column table and you wan to reset it without delete records.
Remove foreign key reference (if any) from YourTabe and set Is Identity to No in column properties windows in Microsoft SQL Server Management Studio. Execute the bellow SQL using Management Studio.
 
USE YourDataBase
 
SELECT [YourTableId],ROW_NUMBER() OVER (ORDER BY YourTableId ) AS RowNumber
    into #tempTable from [YourTable]
 
DECLARE your_table_cursor CURSOR FOR
    SELECT [YourTableId], RowNumber
    FROM #tempTable
 
OPEN your_table_cursor
 
DECLARE @YourTableId int
DECLARE @RowNumber int
 
FETCH NEXT FROM your_table_cursor
INTO @YourTableId, @RowNumber
 
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE  [YourTable]
        SET [YourTableId] = @RowNumber
        WHERE [YourTableId] = @YourTableId
 
    FETCH NEXT FROM your_table_cursor
    INTO @YourTableId, @RowNumber
END
 

CLOSE your_table_cursor
DEALLOCATE your_table_cursor
 
DROP TABLE #tempTable 
 
 
Set Is Identity to Yes and set Identity increment to 1 and Identity Seed to 1 in column properties windows in Management Studio. Then set reference.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abdul Quader Mamun
Team Leader
Bangladesh Bangladesh
I have been developing software/web applications since 2002 mainly on Microsoft technologies.
 
I'm also experienced with other technologies.
 
I'm fan of well organized and effective coding.
 
A wide range of technologies, tools, languages, frameworks, components and design patterns I’m experienced with.
 
I’m a MCP, MCAD.NET, MCSD.NET.
Follow on   Google+

Comments and Discussions

 
GeneralReason for my vote of 2 See alternative PinmemberEskern18-Dec-10 23:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 17 Dec 2010
Article Copyright 2010 by Abdul Quader Mamun
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid