SQLServer – Identity Column – Change Existing Column

Background

We have an existing table what was meant to be have its primary key defined as an Identity Column, but due to an oversight it was not

Existing Table

SQL

sp_help

If we issue, sp_help against the table, here is what it looks like…

SSMS – Table Designer

Here is what we see if we use SSMS Table Designer…

 

Remediation

Outline

  1. Backup Table
  2. Identity Column
    • Add new identity column
  3. Populate identity Column with data from original column
    • Set Identity_insert On
    • Delete data from table
    • Import Data into table
      • New Column = Original Column
    • Set Identity_insert Off
  4. Drop table’s primary key
  5. Drop Original Column
  6. Rename Column
    • Identity Column to Original Column
  7. Re-create primary key
  8. Drop temporary table

 

SQL



set nocount on
set XACT_ABORT on
set noexec off
go


declare @schema	sysname
declare @table sysname
declare @column sysname
declare @tableFull sysname
declare @columnFull sysname

declare @columnNewID sysname
declare @columnNewFull sysname

declare @isIdentity bit
declare @ableToUpdateIdentityColumn bit

declare @commit bit

declare @CHAR_TAB varchar(10)

set @CHAR_TAB = char(9)

set @commit = 0
--set @commit = 1

set @schema = 'dbo'
set @table = 'tGeneratedArticulationDetailExcludedIdentifier'
set @column = 'tArticulationDetailExcludedIdentifierSK'
set @tableFull = quoteName(@schema)  + '.' + quoteName(@table)
set @columnFull = @tableFull + '.' + quoteName(@column)

set @columnNewID = '_identityCol'
set @columnNewFull = @tableFull + '.' + quoteName(@columnNewID)

select @isIdentity = max
						(
							cast(is_identity as tinyint)
						)
from   sys.columns tblSC
where  tblSC.object_id = object_id(@table)

print '@isIdentity ' + cast( @isIdentity as varchar(10))

if (@isIdentity = 1)
begin

	set noexec on

end

if (
		(@isIdentity = 0)
		and not exists
		(
			select  1
			from   sys.columns tblSC
			where  tblSC.object_id = object_id(@table)
		)

	)
begin tran

	if object_id('tempdb..#tGeneratedArticulationDetailExcludedIdentifier') is not null
	begin

		print 'drop table #tGeneratedArticulationDetailExcludedIdentifier ...'

		drop table #tGeneratedArticulationDetailExcludedIdentifier

		print 'dropped table #tGeneratedArticulationDetailExcludedIdentifier'

	end

	print 'Backing up table ...' 

	select *
	into   #tGeneratedArticulationDetailExcludedIdentifier
	from   [dbo].[tGeneratedArticulationDetailExcludedIdentifier] tbl

	print 'Backed up table ' 

	--exec sp_help '[dbo].[tGeneratedArticulationDetailExcludedIdentifier]'

	print @CHAR_TAB + 'Add new column _identityCol ...' 

	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		add [_identityCol] int not null identity(1,1)

	print @CHAR_TAB + 'Set identity column ' + @tableFull + ' on ... '

	set identity_insert [dbo].[tGeneratedArticulationDetailExcludedIdentifier] on

	/*
		Cannot update identity column '_identityCol'.
	*/					
	if (@ableToUpdateIdentityColumn = 1)
	begin

		if exists
		(
			select *
			from   [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		)
		exec('
				update  tbl
				set     [_identityCol] = [tArticulationDetailExcludedIdentifierSK]
				from    [dbo].[tGeneratedArticulationDetailExcludedIdentifier] tbl
			')
	
	end
	else
	begin

		delete
		from   [dbo].[tGeneratedArticulationDetailExcludedIdentifier]

		exec
		('
			insert into [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
			(
					 [tArticulationDetailExcludedIdentifierSK]
				   , [tGeneratedArticulationDetailSK]
				   , [CourseIdentifierParentID]

				   , [_identityCol]
			)
			select
					 [tArticulationDetailExcludedIdentifierSK]
				   , [tGeneratedArticulationDetailSK]
				   , [CourseIdentifierParentID]

				   , [tArticulationDetailExcludedIdentifierSK]

			from #tGeneratedArticulationDetailExcludedIdentifier
		')

	end

	print @CHAR_TAB + 'Set identity column ' + @tableFull + ' off'

	set identity_insert [dbo].[tGeneratedArticulationDetailExcludedIdentifier] off

	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		drop constraint [PK__tGenerat__33CFD59414D00BD0]

	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		drop column [tArticulationDetailExcludedIdentifierSK]


	/*
		print '@columnFull : ' + @columnNewFull
		print '@column : ' + @column
	*/
	EXEC sp_rename
		   @objname = @columnNewFull
		 , @newname = @column
		 , @objtype = 'COLUMN'


	alter table [dbo].[tGeneratedArticulationDetailExcludedIdentifier]
		add constraint [PK_tGeneratedArticulationDetailExcludedIdentifier]
			primary key
			(
				[tArticulationDetailExcludedIdentifierSK]
			)


	if object_id('tempdb..#tGeneratedArticulationDetailExcludedIdentifier') is not null
	begin

		print 'dropping table #tGeneratedArticulationDetailExcludedIdentifier ...'

		drop table #tGeneratedArticulationDetailExcludedIdentifier

		print 'dropped table #tGeneratedArticulationDetailExcludedIdentifier'

	end


print ''

while (@@trancount > 0)
begin

	if (@commit = 1)
	begin

		print 'commit tran'
		commit tran;

	end
	else
	begin

		print 'rollback tran'
		rollback tran

	end

end

go


set noexec off
go

Leave a Reply

Please log in using one of these methods to post your comment:

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