ADD IDENTITY TO COLUMN



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)

Posted in Uncategorized

Leave a Reply

Your email address will not be published. Required fields are marked *