How can i restart from ID=1 ?

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

There is my Database Table such as(tbl_Company),ID=auto increment=1
and there is datas-
ID=1 , Samsung , 098765 ,
ID=2 , Sony , 098976 ,
ID=3 , Apple , 098773 ,

etc

I deleted the (ID=1, Samsung) then,it shows

ID=2 , Sony , 098976 ,
ID=3 , Apple , 098773 ,

I want the Sony Company to show from the begining(ID=1,Sony)

So , How can i do that?

Posted 6-Nov-12 19:18pm

heinhtataung

Updated 6-Nov-12 19:20pm

v3

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Eyakem · Accepted Answer · 2012-11-06T20:40:00

Here is a quick example, i'll walk you through..

1 Lets create a table called UserTable
2 insert four rows
3 delete the first row (so we are where you are now, a table that starts from ID=2

My solution: copy the contents of the table to a temporary table, truncate the origional table and load it back from the temporary table

CAUTION: if the table has relationship (or if the ID that you are changing is foreign key on other table), you have to consider updating all related tables as well.

SQL

-- create a table called UserTable
CREATE TABLE [UserTable](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NOT NULL,
 [UserAddress] [varchar](50) NULL,
CONSTRAINT [PK_UserTable] PRIMARY KEY CLUSTERED
([ID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO

--insert four rows
INSERT INTO [UserTable] ([Name] ,[UserAddress])
VALUES ('John' ,'Address 1'),('Mary' ,'Address 2'),('Doe' ,'Address 3'),('Kent' ,'Address 4')
GO

--delete the first row
Delete From [UserTable] where ID = 1;

--My solution: run the following script to reset the table 
SELECT [Name],[UserAddress] INTO #temp from [UserTable];
Truncate Table [UserTable];
INSERT INTO [UserTable]([Name],[UserAddress]) 
SELECT [Name],[UserAddress] FROM #temp

Sanjay K. Gupta · Accepted Answer · 2012-11-06T20:21:00

Solution 3

Why you need like that? you have already specified that your ID column is Auto incremented column. I think you need a report like this.
Use Ranking function for showing the Sr No.

Posted 6-Nov-12 20:21pm

Sanjay K. Gupta

Comments

heinhtataung 7-Nov-12 3:19am

Thanks

damodara naidu betha 7-Nov-12 7:16am

5+

kankeyan · Accepted Answer · 2012-11-06T20:06:00

you need to reseed the identity column. check for the below example.

Hope this may help you.If helps Vote it.

SQL

USE AdventureWorks
GO
/* Create a table with one identity column */
CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES ('First')
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete last few records */
DELETE
FROM TableID
WHERE ID IN (8,9,10)
GO
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT @MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
GO
/* Insert 10 records with second value */
INSERT INTO TableID (Col)
VALUES ('Second')
GO 5
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO

Thanks

Bhushan Shah1988 · Accepted Answer · 2012-11-06T20:03:00

Solution 1

i think it is not possible.

and if it is must required than you need to do it with programming.

what you need to do is first select data from table in datatable
then delete all data from table.
now iterate through datatable and insert data into that table.

Posted 6-Nov-12 20:03pm

Bhushan Shah1988

Comments

Abhishek Pant 7-Nov-12 2:07am

but what would happen if any counter to shift the id is added to that column.

SoumenBanerjee 7-Nov-12 2:51am

I think delete all data from table then insert will insert from ID 4, use truncate rather than using delete

heinhtataung 7-Nov-12 3:19am

thanks

How can i restart from ID=1 ?

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0