Reseting the idenity seed to start at 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Reseting the idenity seed to start at 1

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2015-03-06 : 15:21:54

I have the following:
CREATE TABLE [Lookup].[Countries](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](2) NOT NULL,
[Name] [nvarchar](70) NOT NULL
) ON [PRIMARY]

GO
I need to fix the below so the identity starts at 1 instead of 248
-----
Id Code Name
248 AD Andorra
249 AE United Arab Emirates
250 AF Afghanistan
251 AG Antigua And Barbuda
252 AI Anguilla
253 AL Albania
254 AM Armenia
255 AO Angola
256 AQ Antarctica
257 AR Argentina
258 AS American Samoa
259 AT Austria
260 AU Australia
261 AW Aruba
262 AX Aland Islands

Any help would be awesome.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-03-07 : 08:31:21

If the data is already in the table, for small to moderate sized tables, the easiest way is to repopulate the table.

-- save the data. After this step, make sure that you have the data in the new table.
select * into  dbo.ATempTable from [Lookup].[Countries];
GO
-- truncate table. This also resets the identity seed.
truncate table  [Lookup].[Countries];
GO

-- re-insert the data
insert into  [Lookup].[Countries] select Code, Name from  dbo.ATempTable;
GO

-- verify that you have the right data in the original table, and then drop it.
drop table dbo.ATempTable;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-07 : 13:12:30

First and most important question of them all is "Why does it matter?".
It's a surrogate key and as such, no logic should be placed onto it.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources