SQL Set IDENTITY Field Using Variable

Question

All, I want to start the numbering of an IDENTITY field based on the current maximum obtained from another table. So I have tried something like the following

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

But this will not accept a variable as the seed value in IDENTITY. How can what I require be achieved?

Thanks for your time.

Lemures

Answer 1

Do do this and other non-variable allowed tasks, you can use the EXEC function, as follows:

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)

Ariella

one should avoid dynamic SQL as much as possible. In this case there a non-dynamic alternative proposed by freefaller – Rhadamanthus

Answer 2

You could use the dbcc checkident feature of SQL Server...

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

One thing to note with this is that the value in the 3rd parameter (in this case the @MAXID variable) denotes the current identity value - in other words the last identity value that was generated on the table.

So, for example, if you want the next value that is automatically created to be 100, then set the 3rd parameter to 99.

Ashantiashbaugh

watch out for this: checkident results in different behaviour if your table has never had data in it - #473078 – Hammers

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;

OR (if the id column is of type 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

Harrar

you should use scope_identity() not @@IDENTITY, the latter isn't scoped to your script so can give unexpected results – Hammers

Answer 4

Try something like this..

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

Sanskritic