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
you can use -
alter table <mytable> add ident INT IDENTITY
This adds ident column to your table and adds data starting from 1 and incrementing by 1.
To add clustered index -
CREATE CLUSTERED INDEX <indexName> on <mytable>(ident)
have 1 approach in mind, but not sure whether it is feasible at your end or not. But let me assure you, this is a very effective approach. You can create a table having an identity column and insert your entire data in that table. And from there on handling any duplicate data is a child's play. There are two ways of adding an identity column to a table with existing data:
Create a new table with identity, copy data to this new table then drop the existing table followed by renaming the temp table.
Create a new column with identity & drop the existing column
For reference the I have found 2 articles : http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/ http://cavemansblog.wordpress.com/2009/04/02/sql-how-to-add-an-identity-column-to-a-table-with-data/
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)')
