Adding a Primary Key to a Prepopulated Table using a SSIS Package

By: Haroon Ashraf | Comments | Related: More > Integration Services Development

Problem

A SQL Server table was created long ago without a primary key probably as a result of an import. It began to get populated over time. As a SQL Server Developer, I have been tasked to automate the process of adding a primary key to this table (without removing duplicate rows) in such a way that the Primary Key column becomes the first column of the table and there should be no data loss during this operation.

Solution

This solution is based on the idea of creating a new table based on existing table with an additional auto increment id column set as the primary key and then copying the data from the existing table to the new table which has a primary key.

The script can be created and run manually, but we can automate the process by using Integration Services.

This tip is focused on creating a backup table with primary key followed by creating and running an Integration Services package to achieve the goal.

Adding SQL Server Primary Key to a Table using an SSIS Package

We are ready to add a primary key to a table which does not have a primary key.

Prerequisites

This tip assumes that SQL Server Data Tools (SSDT) with the latest version of Visual Studio has already been installed and the readers are familiar with SQL Server Data Tools (SSDT).

Steps Summary

The steps can be summarized as follows:

Creating a sample database
Creating a table without primary key in the sample database
Populating the table
Renaming the table as old table
Creating a new table based on old table
Adding primary key with identity column to the new table
Creating an SSIS Package to copy data from old table to new table with primary key
Automating the process through SSIS Package by running the SSIS Package

This is illustrated as follows:

Sample Database Setup

Open SQL Server Object Explorer (SSOX) and Add New Database called “University” or alternatively use the following script to create the sample database:

-- Creating Sample Database University
CREATE DATABASE University

Let us use University database created earlier on to create the Course table.

Right click University and click New Query…:

Writing the following code and execute to create the Course table:

-- Creating course table
CREATE TABLE [dbo].[Course](
    [Name]     VARCHAR(30)  NOT NULL,
[Detail] VARCHAR(200) NULL,
);
GO

Populate the table by using the following script:

-- Populating course table without primary key
INSERT INTO [dbo].[Course]([Name], [Detail]) VALUES(N'DevOps for Databases', N'This is about DevOps for Databases')
INSERT INTO [dbo].[Course]([Name], [Detail]) VALUES(N'Power BI Fundamentals', N'This is about Power BI Fundamentals')
INSERT INTO [dbo].[Course]([Name], [Detail]) VALUES(N'T-SQL Programming', N'About T-SQL Programming')
INSERT INTO [dbo].[Course]([Name], [Detail]) VALUES(N'Tabular Data Modeling', N' This is about Tabular Data Modeling')
INSERT INTO [dbo].[Course]([Name], [Detail]) VALUES(N'Analysis Services Fundamentals', N'This is about Analysis Services')

View the data:

Renaming Course Table

Right click on the Course table and click New Query and write and execute the following script to rename the existing table Course as Course_Old:

-- Renaming Course table as Course_Old
EXEC sp_rename "Course","Course_Old"

Creating New Course Table with a Primary Key

Create a new table using the Query window and execute the following script against the University database:

-- Creating new Course table with primary key and auto increment identity column
CREATE TABLE [dbo].[Course](
   [CourseId] INT IDENTITY(1,1),
   [Name]     VARCHAR(30)  NOT NULL,
   [Detail] VARCHAR(200) NULL,
   CONSTRAINT [PK_Course] PRIMARY KEY([CourseID])
);
GO

Create a New SSIS Project

Create a new Integration Services Project and call it PrimaryKeyProject:

Adding Data Flow Task

Add a Data Flow Task and drag and drop the OLE DB Source and OLE DB Destination:

Configure Source and Destination

Configure database connection to point to the University database:

Select Course_Old table as the Source:

Join the OLE DB Source with the OLE DB Destination to create a pipeline:

Configure the OLE DB Destination component to point to a newly created Course table which contains the primary key:

Check the column mappings by clicking Mappings on the left side and please note that CourseId in the mappings will be ignored since it is only present in the destination table:

Running the SSIS Package

Run the SSIS Package to start the data transfer from old table to new table:

Viewing Data

Go to SQL Server Object Explorer (SSOX) and right click Course and click View Data:

Congratulations! You have successfully added a primary key column (in the first column) to the table without losing any data.

Once you are certain all rows have been successfully copied to the new table you can delete the old table.

Next Steps

Please try adding duplicate records in original course table and then use Sort transformation in SSIS Package to remove duplicate rows during the data transfer and see if you can successfully add primary key to a table which was originally created without primary key along with removing duplicate rows. (Hint: please refer to Removing Duplicates Rows withSSIS Sort Transformation tip)
Please refer to my previous tip Adding a Primary Key to a Prepopulated Table using SQL Server Object Explorer in SSDT and try adding Student table and link it with Course table through CourseId and then add primary keys to both tables using the solution mentioned in this tip
Please refer to my previous tip Developing Similar Structured Multi-Customer Databases with SQL Server Data Tools (SSDT) and try to create Client, Service and ServiceOrder tables without primary and foreign keys and then populate these tables and finally add primary and foreign keys using the method followed by creating and running SSIS Package in this tip
Please refer to my previous tip SQL Server Reference Data Best Practices - Part 1 and try creating reference tables CarMake and CarType without primary keys then add primary key by implementing the approach mentioned in this tip.

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Adding a Primary Key to a Prepopulated Table using a SQL Server Integration Services Package