CODEWITHSUNDEEP
sql-server-2008t-sqldynamic-sqlidentity-column
MoonKnight
MoonKnight

Reputation: 23833

SQL Set IDENTITY Field Using Variable

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.

Upvotes: 7

Views: 27994

Answers (4)

user1786058
user1786058

Reputation: 

--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

Upvotes: 2

freefaller
freefaller

Reputation: 19953

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.

Upvotes: 8

Joon
Joon

Reputation: 2147

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)

Upvotes: 9

Shailesh
Shailesh

Reputation: 1218

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

Upvotes: 1

Related Questions