SQL Set IDENTITY Field Using Variable

Viewed 24209

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.

4 Answers
Related