使用变量设置SQL的IDENTITY字段

Question

大家好，我希望能够根据另一张表中当前的最大值来开始一个IDENTITY字段的编号。因此，我尝试了以下的方法：

DECLARE @CurrentES INT;
SET @CurrentES = (SELECT MaxES 
                  FROM [NDB]..[TmpMaxES]) + 1;
ALTER TABLE BA 
ADD ES INT IDENTITY(@CurrentES, 1);

但是IDENTITY不支持使用变量作为种子值。如何实现我需要的功能呢？

感谢您的时间。

- MoonKnight

Answer 1

要执行此类不允许使用变量的任务，您可以使用EXEC函数，如下所示：

DECLARE @CurrentES INT;
SET @CurrentES = (SELECT MaxES 
                  FROM [NDB]..[TmpMaxES]) + 1;

DECLARE @Statement VARCHAR(200)

SET @Statement = 'ALTER TABLE BA 
ADD ES INT IDENTITY(' + CAST(@CurrentES AS VARCHAR) + ', 1);'

EXEC (@Statement)

- Joon

---

应尽可能避免使用动态SQL。在这种情况下，Freefaller提出了一种非动态的替代方案。 - Greg Z.

Answer 2

您可以使用 SQL Server 的 dbcc checkident 功能...

DECLARE @MAXID INT
SELECT @MAXID = MAX(ID_FIELD) FROM OLDTABLE
dbcc checkident(NEWTABLE, reseed, @MAXID)

需要注意的一点是，第三个参数中的值（在本例中为@MAXID变量）表示当前标识值，换句话说，是上一次在表上生成的标识值。

因此，例如，如果您希望下一个自动生成的值为100，则将第三个参数设置为99。

- freefaller

---

注意：如果您的表从未有过数据，使用checkident会导致不同的行为 - https://dev59.com/uHRB5IYBdhLWcg3w6LR2 - Tim Abell

Answer 3

--first variable 
    declare @code varchar(50);
    set @code=1345688867567576;
--second variable
    declare @namedb varchar(50);
    set @namedb='test';
--let's you add to the identity(ID) field           
    SET IDENTITY_INSERT dbo.nameAndroid ON
--declaring variable to hold the next id number
    declare @id int;
    set @id=@@IDENTITY +1;
--clause to check if the table has the matching barcode 
    if not exists (select * from dbo.nameAndroid where barcode = @code)
    INSERT INTO dbo.nameAndroid (id, name, barcode, [floor], Column1,Column2,Row1,Row2,Shelf,Stock,OnOrder)
    VALUES ( @id,@namedb, @code, 'Value3', 'Value4','Value5','Value6','Value7','Value8',123,600);

    SET IDENTITY_INSERT dbo.nameAndroid OFF;

或者（如果id列是int类型）

    declare @code varchar(50);
    set @code='123211';

    declare @namedb varchar(50);
    set @namedb='test';

    declare @floordb varchar(50);
    set @floordb='test';

    declare @Column1db varchar(50);
    set @Column1db='test';

    declare @Column2db varchar(50);
    set @Column2db='test';

    declare @Row1db varchar(50);
    set @Row1db='test';

    declare @Row2db varchar(50);
    set @Row2db='test';

    declare @Shelfdb varchar(50);
    set @Shelfdb='test';

    declare @OnOrderdb decimal(18,2);
    set @OnOrderdb=10010;

    declare @Stockdb decimal(18,2);
    set @Stockdb=1010101;

    declare @id int;
   set @id=((select max(id) from dbo.nameAndroid )+1);



if not exists (select * from dbo.nameAndroid where barcode = @code)

  begin
SET IDENTITY_INSERT dbo.nameAndroid ON;

    INSERT INTO dbo.nameAndroid (id, name, barcode, [floor], Column1,Column2,Row1,Row2,Shelf,Stock,OnOrder)
    VALUES (@id, @namedb, @code, @floordb, @Column1db,@Column2db,@Row1db,@Row2db,@Shelfdb,@OnOrderdb,@Stockdb);

SET IDENTITY_INSERT dbo.nameAndroid OFF;
end

- user1786058

---

你应该使用 scope_identity() 而不是 @@IDENTITY，后者没有限定到你的脚本，因此可能会产生意外的结果。 - Tim Abell

Answer 4

尝试类似这样的东西...

SET IDENTITY_INSERT [MyTable] ON
INSERT INTO [MyTable] ... (MAX) Value from another table and other applicable record.
...
SET IDENTITY_INSERT [MyTable] OFF

- Shailesh