Question

利用服务器

表1

ID Name

001 Raja
002 Ravi
003 Suresh
004 Kamal
...

我需要设立一个新的表格,为每个栏目确定身份

Tried query

insert into table2 
  select * from table1

预期产出

S.No ID Name

1 001 Raja
2 002 Ravi
3 003 Suresh
4 004 Kamal
...

I create the new table and made the S.No column is identity in the table. It is creating the identity number for each row, then I delete all the row, again inserted, but identity is created from last row next number onwards.

例

S.No ID Name

    1 001 Raja
    2 002 Ravi
    3 003 Suresh
    4 004 Kamal
    ...

删除所有各行,我再次插入。

S.No ID Name

    5 001 Raja
    6 002 Ravi
    7 003 Suresh
    8 004 Kamal
    ...

预期产出

身份应从第1号开始,只要我从表格中删除所有各行。

问询

Answer 1

这并不是身份如何发挥作用。拆除路障后,重新打造了身份领域。你们必须重新看到:

DBCC CHECKIDENT ( ‘databasename.dbo.yourtable’,RESEED, 0) -- will start from 1

Answer 2

Or just truncate the table? That will reset the primary key, too.
(marc_s: actually, it s the IDENTITY value that it s resetting)

TRUNCATE TABLE tablename

Answer 3

如果您想要S.No.R.R.R.R.R.R.R.R.R.R.R.R.R.R.R.R.R.,N.R.C.R.R.R.R.R.R.R.R.R.R.R.R.R.M.,P.R.R.R.R.R.R.R.R.R.M.,P.R.R.R.R.,

因此,你可以发挥cal的作用,找到这一价值,将DefaultValue列列到这一功能中,并设定identity to false。

查阅这一样本:

CREATE FUNCTION [dbo].[getmaxno]()
RETURNS int
AS
BEGIN
    DECLARE @ResultVar int
    SELECT @ResultVar = MAX(no)+1 from dbo.table_1
    RETURN @ResultVar
END

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_no]  DEFAULT ([dbo].[getmaxno]()) FOR [no]

友情链接