ADD IDENTITY TO COLUMN

26Nov08

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>)

Advertisements


2 Responses to “ADD IDENTITY TO COLUMN”

  1. 1 advactfrado

    I’m the only one in this world. Can please someone join me in this life? Or maybe death…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: