If you have an existing table (in my case products)
This is the code you ll use to Alter a column and Add Identity to it. This is the Only Solution found on the NET. Even SSIS Cant to this procedure automatically. I guess this is caused due to constrains bound to a table.
-- ALTER COLUMN ID TO INT ALTER TABLE [dbo].[Products] ALTER COLUMN [Code] int NOT NULL; GO -- RENAME THE TABLE TO A TEMP TABLE TO HOLD THE DATA (ANY DEPENDENCIES WILL BE LOST) EXEC sp_rename 'Products', 'ProductsTemp' GO --CREATE TABLE WITH IDENTITY CREATE TABLE [dbo].[Products] ( [Code] INT IDENTITY PRIMARY KEY, [Descr] nvarchar(250), [Price] money, ) GO -- SET IDENTITY INSERT ON SET IDENTITY_INSERT [dbo].[Products] ON; GO -- COPY DATA INTO NEW TABLE INSERT INTO [dbo].[Products] ([CodeID] ,[Descr] ,[Price] ) SELECT [CodeID] ,[Descr] ,[Price] FROM [dbo].[ProductTemp] GO -- DROP TEMP TABLE DROP TABLE [ProductsTemp]; GO -- SET IDENTITY INSERT OFF SET IDENTITY_INSERT [dbo].[Products] OFF; GO -- Run DBCC to reset the identity value on the table DBCC CHECKIDENT (Products, RESEED) GO
— ** REMEMBER TO CHECK THE IDENTITY SEED OR ELSE
— FIND MAX CodeID AND Choose this DBCC CHECKIDENT (Products, RESEED, MaxIdFound)