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.

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)