· 8 years ago · Apr 06, 2018, 06:46 AM
1USE TempDB
2
3-- Check if the table exists and if not then create it.
4IF NOT EXISTS(SELECT *
5 FROM Sys.Tables
6 WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[#TableWithDups]'))
7
8 -- Create temp table
9 BEGIN
10 CREATE TABLE #TableWithDups(
11 [PK] TINYINT NOT NULL PRIMARY KEY,
12 [EMail] [varchar](40) NULL,
13 [First] [varchar](15) NULL,
14 [Last] [varchar](20) NULL,
15 [Gender] [varchar] (1) NULL,
16 [DOB] [varchar](8) NULL,
17 [Phone] [varchar](10) NULL,
18 [StreetNumber] [varchar](30) NULL,
19 [StreetName] [varchar](30) NULL
20 )
21 END
22-----------------------------------------------------------------
23-- Check if some temp tables exist in the TempDB database -------
24-----------------------------------------------------------------
25ELSE
26BEGIN
27 TRUNCATE TABLE #TableWithDups
28END
29
30IF EXISTS(SELECT *
31 FROM Sys.Tables
32 WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[#DeDup_Testing]'))
33BEGIN
34 DROP TABLE #DeDup_Testing
35END
36
37--------------------------------------------------------------------
38--- Insert duplicates records into TableWithDups
39--------------------------------------------------------------------
40INSERT INTO #TableWithDups ([PK],[EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
41VALUES (1,'MSSQL1@RemoveDupRecords.Com','Susan','Smith','F', '19600912','','1100','Main Street')
42
43INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
44VALUES (2,'MSSQL1@RemoveDupRecords.Com','Susan','Smith','', '','1113058336','1100','Main Street')
45
46INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
47VALUES (3,'MSSQL1@RemoveDupRecords.Com','Susan','Smith','F','','1113058336','1100','Main Street')
48
49INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
50VALUES (4,'MSSQL2@RemoveDupRecords.Com','Joe','Smith','','19640825','2223058336','1100','Main Street')
51
52INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
53VALUES (5,'MSSQL2@RemoveDupRecords.Com','Joe','Smith','M','19640825','2223058336','1100','Main Street')
54
55INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
56VALUES (6,'MSSQL3@RemoveDupRecords.Com','Alex','Walker','M','','5553058888','55','KingFisher Road')
57
58INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
59VALUES (7,'MSSQL3@RemoveDupRecords.Com','Alex','Walker','F','19580912','','55','KingFisher Road')
60
61INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
62VALUES (8,'MSSQL3@RemoveDupRecords.Com','Alex','Walker','','1952','','55','KingFisher Road')
63
64INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
65VALUES (9,'MSSQL4@RemoveDupRecords.Com','Uri','Pearl','M','1961','3333054422','110','Robertsville Road')
66
67INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
68VALUES (10,'MSSQL4@RemoveDupRecords.Com','Uri','Pearl','','19600912','3333054422','110','Robertsville Road')
69
70INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
71VALUES (11,'MSSQL4@RemoveDupRecords.Com','Uri','Pearl','','','','110','Robertsville Road')
72
73INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
74VALUES (12,'MSSQL5@RemoveDupRecords.Com','Terry', 'Thurber','M','','4443058377','500','Larkin Street')
75
76INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
77VALUES (13,'MSSQL5@RemoveDupRecords.Com','Terry', 'Thurber','','19600809','','500','Larkin Street')