How to convert Int column to Identity in the MS SQL Server

  • Article
 Table of Content
Introduction
Scenario
Steps

Introduction:
This topic has been discussed many times in various forums but when I faced the problem, it appears all are scattered in bits & pieces. Here I will try to elaborate the steps by one example for the person who reached this page. I am sure seasoned DB admins and developers are aware of these steps.

Scenario:
I have a table with two columns ExampleID & Example. This ExampleID column is of Int type and it has been in use for decades. Now suddenly for some reason you wish to convert ExampleID column to Identity column. But, you already referred this ExampleID column in numerous tables. So, you need to convert & preserve the values. ALSO, you wish it to work (i.e., create new ExampleIDs) only with newly inserted records going forward!

We will do that here by creating a new Identity column and insert old values from Int column and then finally drop the Int column.

Steps:
If you are working on the production environment, please ensure you have the provision of downtime for this table.
* Please replace all the entries inside <> brackets including the angle brackets with real values.

Option 1: When you are working on the same instance of the SQL Server.

  1. Import the existing table to a new table.

a. Right click on the database in the SQL Server Management Studio (SSMS) and select Tasks.
b. Click on the Import data to launch the SQL Server Import and Export Wizard.
c. In the SQL Server Import and Export Wizard, ensure you select Source and Destination connection settings for the server you are working on. That is, both should point to the same server. Click Next.
d. On the Specify Table Copy or Query window, select the radio button Copy data from one or more tables or views. Click Next.
e. On Select Source Tables and Views window, select the source table name. It will automatically try to fill in the destination table name. Click on the destination table name and append it with _New. Hence destination table name in our example will be Example_New. Click Next.
f. On the Save and Run Package window, select the check box Run immediately. Click Next and then Finish.

2. Now you got the replica of the table with data. For other columns with constraints, indexes etc you may script those and implement on this new table at later stage from SSMS. Now let us create an identity column on this new table and remove the old Int column.

USE [<dbname>] 
ALTER Table  [DBO].[Example]
Add ExampleID_New Int Identity(1, 1) -- change the column name as per your requirement and seed value.
GO
ALTER Table  [DBO].[EXAMPLE_New] Drop Column  ExampleID
GO
Exec sp_rename '[DBO].[EXAMPLE_New].ExampleID_New', 'ExampleID'
  1. Please start the SQL Server Import and Export Wizard again.
  2. Connection settings will same as we did earlier and on the Select Source Tables and Views window, select the source table name (in our case: [Example]) and select the destination table name as [Example_New].
  3. Click on the Edit Mappings. Select the check box Enable Identity Insert and select Delete rows in destination tables.
  4. Below in the Mappings grid, ensure that columns are mapped one to one with same names. Click OK.
  5. Click on Next on the Select Source Tables and Views window and complete the execution of wizard by selecting Run immediately.
  6. Now you should have Example_New table with one additional column. We will now set the record straight.
DROP TABLE  [DBO].[Example]
Exec sp_rename '[DBO].[EXAMPLE_New]', 'Example'

9. Now verify the Example table. Here you have ExampleID column with old values but it is now an Identity column!

Option 2: When you are creating a new table or altering an existing table based on the data residing in a different SQL SERVER instance.

  1. Run the following statements on the destination table (on the destination server/instance) to create an Identity column and drop the existing Int column.
USE [<dbname>] 
ALTER Table  [DBO].[Example]
Add ExampleID_New Int Identity(1, 1) -- change the column name as per your requirement and seed value.
GO
ALTER Table  [DBO].[EXAMPLE] Drop  Column ExampleID
GO
Exec sp_rename '[DBO].[EXAMPLE].ExampleID_New', 'ExampleID',  'Column'

2. Please start the SQL Server Import and Export Wizard (Right click on the database name in SSMS, click Tasks->Import Data).
3. Apply source and destination server connection settings and on the Select Source Tables and Views window, select the source table name (in our case: [Example]) and select the destination table name as [Example].
4. Click on the Edit Mappings. Select the check box Enable Identity Insert and select Delete rows in destination tables.
5. Below in the Mappings grid, ensure that columns are mapped one to one with same names. Click OK.
6. Click on Next on the Select Source Tables and Views window and complete the execution of wizard by selecting Run immediately.
7. Now verify the Example table in the destination server/instance. Here you have ExampleID column with old values but it is now an Identity column!