28 Feb

Reseed Azure SQL table

Microsoft SQL Azure
 

As said in one of our earlier post, Azure SQL has some flaws.
Another one is that you can’t reseed an Azure SQL table. This is because DBCC CHECKIDENT is not currently supported in Windows Azure SQL Database.
So in this post I’ll show one work around to handle this problem.


 

The idea

The idea behind this is that we’ll stop the auto-identity and insert our data at the exact spot where we want it. From there on we’ll turn it back on so the auto-identity will continue where it left off.

SET IDENTITY_INSERT [table name] ON GO will turn of the auto-identity.

SET IDENTITY_INSERT [table name] OFF GO will turn the auto-identity back on so it can continue.



The code

SET IDENTITY_INSERT [table name] ON
GO
 
INSERT INTO [table name]([identity column],...,....) VALUES([startid],...,...)
GO
 
SET IDENTITY_INSERT [table name] OFF
GO

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.