Sunday, March 17, 2013

Reseed Table Identity without Truncate Table

Here i am sharing a simple example to reseed identity, i hope it will help you.

-------------------------Copy and Paste Below script in Sql query window
--Step 1                        
IF OBJECT_ID('TempDB..#tmp','U') IS NOT NULL
begin
    drop table #tmp
end

Create Table #tmp(ID Int identity(1,1),Cnt int)

--Step 2: Insert Data
Insert into #tmp(Cnt)
Select 1 as Cnt
union
Select 2
union
select 3

--Step 3: Delete Record to Check Reseed
Delete from #tmp where Cnt=3

--Step 4: Reseed Identity
Declare @maxID int
Select @Maxid=max(ID) from #tmp
print @MaxID

DBCC CHECKIDENT ('#tmp', RESEED, @MaxID)

--Step 5: Insert again and check Identity
Insert into #tmp(Cnt)
Select 4 as Cnt
union
Select 5
union
select 6

If you are searching life partner. your searching end with kpmarriage.com. now kpmarriage.com offer free matrimonial website which offer free message, free chat, free view contact information. so register here : kpmarriage.com- Free matrimonial website