SQL Set IDENTITY Field Using Variable

sql-server-2008 tsql dynamic-sql identity-column
MoonKnight picture MoonKnight · Jul 24, 2012 · Viewed 21.4k times · Source

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

Joon picture Joon · Jul 24, 2012

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)

Related questions

exec sp_executesql @sql and exec (@sql) SQL Server

A Dynamic SQL query from lobodava is: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, COLUMN_NAME) as ( select ORDINAL_POSITION, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'''+ @tableName + ''' and COLUMN_NAME like ''' + @columnLikeFilter + ''' ), …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More