我如何从ID = 1重新启动? [英] How can i restart from ID=1 ?

问题描述

有我的数据库表，例如(tbl_Company)，ID =自动增量= 1
并有数据-
ID = 1，三星，098765，
ID = 2，索尼，098976，
ID = 3，苹果，098773，

等

我删除了(ID = 1，Samsung)然后，它显示

ID = 2，索尼，098976，
ID = 3，苹果，098773，


我希望Sony公司从头开始展示(ID = 1，Sony)

那么，我该怎么做呢?

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?

推荐答案

您需要重新设置Identity列的种子.检查以下示例.

希望对您有帮助.如果有帮助，请投票.

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

这里是一个简单的示例，我将带您逐步了解..

1让我们创建一个名为UserTable
的表 2插入四行
3删除第一行(所以我们现在就在这里，一个从ID = 2开始的表

我的解决方案:将表的内容复制到临时表，截断原始表，然后将其从临时表加载回

注意:如果表具有关系(或者您要更改的ID是其他表上的外键)，则还必须考虑更新所有相关表.

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

为什么需要这样?您已经指定ID列为自动递增"列.我认为您需要这样的报告.
使用排名功能显示高级编号

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.

这篇关于我如何从ID = 1重新启动?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！