Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - How to Change (Reseed) Identity Column Value in SQL Server 2008

May 14, 2014
Introduction:

Here I will explain how to change identity column seed value in SQL server 2008 or reseed identity column seed value of table in SQL server or how to change identity column value in SQL server.
Description:

In previous post I explained how set identity or auto increment column in SQL server, Custom auto increment column in sql server, resent identity column in sql server and many articles related to SQL server. Now I will explain how to change or reseed identity column value in SQL server.  I have a one table in that I set identity property on particular column and inserted few records in table while inserting new records identity column value automatically increased that would be like this

If you observe above table countryid value increasing +1 when new record inserting. Suppose if I want to change that identity column value which starts from 500 then I change that seed value for that we need to write the query like as shown below

DBCC CHECKIDENT (Table_Name, RESEED, New_Reseed_Value)
Table_Name is name of your identity column table

RESEED specifies that the current identity value should be changed.

New_Reseed_Value is the new value to use as the current value of the identity column.   
  
Example

EX: DBCC CHECKIDENT ('UserDetails', RESEED, 500)
Once we run above query it will reseed identity column value in UserDetails table and starts identity column value from “500

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

6 comments :

Anonymous said...

Nice Helpful Post Mr Dasari..Sat

Sanjana said...

Dear Sir , I am facing a problem in project,using database MySQL .

There is any query like "Select * into Temp_tb from Old_tb" in MySQL.................or some Similar query plz plz Provide

Anonymous said...

sir when i delete id =1 value again when i insert the record i want taht the record with id=1 should inserted how to do this sir?

Sanjana said...

Dear Sir, I think you have not Updated Yourself ,that's why no replying my answer

Srinu Reddy said...

Hi Sanajana, See this can help you.

CREATE TABLE TEMPTBALENAME
(
OrderDate datetime,
CustomerId int
)
SELECT OrderDate,CustomerId INTO TEMPTBALENAME

FROM dbo.[Order]

SELECT * FROM TEMPTBALENAME

Feel free to ask for further queries.

Thanks,
Srinu Reddy.

Sql Questions said...

really appreciate

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.