SQL Set IDENTITY Field Using Variable
dynamic-sql, identity-column, sql-server-2008, t-sql
Solution
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)
Problem
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.