Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL2000 SQL-server-2005 SQL , +
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 20:18pm
Edited 6-Nov-12 20:20pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

you need to reseed the identity column. check for the below example.
 
Hope this may help you.If helps Vote it.
 
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
  Permalink  
Comments
heinhtataung at 7-Nov-12 3:19am
   
Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
 
-- 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
  Permalink  
v4
Comments
heinhtataung at 7-Nov-12 3:19am
   
Thanks :)
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
heinhtataung at 7-Nov-12 3:19am
   
Thanks
damodara naidu betha at 7-Nov-12 7:16am
   
5+
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
Abhishek Pant at 7-Nov-12 2:07am
   
but what would happen if any counter to shift the id is added to that column.
Soumen_Banerjee at 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 at 7-Nov-12 3:19am
   
thanks

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

  Print Answers RSS
0 OriginalGriff 195
1 Afzaal Ahmad Zeeshan 132
2 ProgramFOX 130
3 Maciej Los 105
4 Sergey Alexandrovich Kryukov 75
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,657
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 7 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100