How to add a new identity column to a table in SQL Server?

user_63235 0
Viewed 62665

I am using SQL Server 2008 Enterprise. I want to add an identity column (as unique clustered index and primary key) to an existing table. Integer based auto-increasing by 1 identity column is ok. Any solutions?

BTW: my most confusion is for existing rows, how to automatically fill-in new identity column data?

thanks in advance, George

sql-server-2008 tsql
3 Answers

Not always you have permissions for DBCC commands.

Solution #2:

create table #tempTable1 (Column1 int)
declare @new_seed varchar(20) = CAST((select max(ID) from SomeOtherTable) as varchar(20))
exec (N'alter table #tempTable1 add ID int IDENTITY('+@new_seed+', 1)')
user_54670620
Related