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.