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.
[sourcecode lang=”sql”]
— 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
[/sourcecode]
— ** REMEMBER TO CHECK THE IDENTITY SEED OR ELSE
— FIND MAX CodeID AND Choose this DBCC CHECKIDENT (Products, RESEED, MaxIdFound)