Thursday, August 15, 2013

SQL Server script to adding an identity to an existing column

As every SQL Server developer knows you can't alter the existing columns for identity. But there is an work around to solve this limitation. You can create a new table with identity, then migrate data form old to new table and then drop the old table. And here follows the script snippet:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tmp_Projects](
 [Id] [bigint] NOT NULL IDENTITY(1,1),
 [Name] [nvarchar](250) NOT NULL,
 [WebsiteUrl] [nvarchar](200) NULL,
 [IsNew] [bit] NOT NULL,
 [CreatedOn] [datetime] NOT NULL,
 [CreatedBy] [varchar](50) NOT NULL,
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT dbo.[tmp_Projects] ON
GO

IF EXISTS ( SELECT * FROM dbo.[Projects] )
 INSERT INTO dbo.[tmp_Projects] (
  [Id]
  ,[Name]
  ,[WebsiteUrl]
  ,[IsNew]
  ,[CreatedOn]
  ,[CreatedBy]
 )
 SELECT  
  [Id]
  ,[Name]
  ,[WebsiteUrl]
  ,[IsNew]
  ,[CreatedOn]
  ,[CreatedBy]
 FROM dbo.[Projects] TABLOCKX
GO

SET IDENTITY_INSERT dbo.[tmp_Projects] OFF
GO

DROP TABLE dbo.[Projects]
GO

Exec sp_rename 'tmp_Projects', 'Projects'
GO