This discussion of finding duplicates in SQL Server is a continuation of Part 1.

We will look at two simple tables. Our first table is an “old” table with a history of all records. We have a “new” table with incoming records that need to be added to the old table. Before adding the records in the new table to the records in the old table we need to check to see if there are any duplicates. We want to know if there are any records in the new table that already exist in the old table. We are focusing only on a single column in this case. We have named the column UnqCode. Here are our two tables built with T-SQL code. In a non-production environment you could run this script to see for yourself how this works.

USE [DuplicateExample]
GO
CREATE TABLE [dbo].[tblNew](
	[UnqCode] [varchar](6) NULL,
	[FirstName] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT INTO tblNew (UnqCode, FirstName) VALUES ('883419','Bob');
INSERT INTO tblNew (UnqCode, FirstName) VALUES ('230817','Suzie');
INSERT INTO tblNew (UnqCode, FirstName) VALUES ('342318','Linda');
CREATE TABLE [dbo].[tblOld](
	[UnqCode] [varchar](6) NULL,
	[FirstName] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT INTO tblOld (UnqCode, FirstName) VALUES ('692177','Mark');
INSERT INTO tblOld (UnqCode, FirstName) VALUES ('883419','Jack');
INSERT INTO tblOld (UnqCode, FirstName) VALUES ('509321','Billy');
INSERT INTO tblOld (UnqCode, FirstName) VALUES ('692177','Jill');
INSERT INTO tblOld (UnqCode, FirstName) VALUES ('290872','Sally');
INSERT INTO tblOld (UnqCode, FirstName) VALUES ('428719','Sam');

After setting up the two tables we can create a stored procedure that will show our duplicates.

CREATE PROCEDURE [dbo].[FindDuplicateCodes] 
AS
BEGIN
	declare @dup int;
	set @dup = 0;

	select tblNew.UnqCode, tblNew.FirstName from tblNew
	left join tblOld on tblNew.UnqCode = tblOld.UnqCode
	where tblOld.UnqCode IS NOT NULL;

	select @dup = COUNT(*) from tblNew
	left join tblOld on tblNew.UnqCode = tblOld.UnqCode
	where tblOld.UnqCode IS NOT NULL;
	print 'Number of duplicates: ' + cast(@dup as varchar(5));
END
GO

After running the stored procedure, here are our results in SSMS. “883419” already exists in the Old table. Jack is 883419.