sample.sql

SQL中重复记录的查询和删除方法

/**********************************************
Set up test environment
**********************************************/
SET NOCOUNT ON

--Create test table
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO

CREATE TABLE tDupData
(
lngCompanyID INTEGER 
,strCompanyName VARCHAR(20)
,strAddress VARCHAR(10)
,dtmModified DATETIME
)

--Create test data
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address2','01/15/2003')
INSERT INTO tDupData VALUES (3,'CompanyThree','Address3','01/15/2003')
INSERT INTO tDupData VALUES (2,'CompanyTwo','Address','01/16/2003') 
INSERT INTO tDupData VALUES (3,'CompanyThree','Address','01/16/2003')

-- Dup Data 
INSERT INTO tDupData VALUES (1,'CompanyOne','Address1','01/15/2004') 
GO

/**********************************************
Finish set up
**********************************************/

/**********************************************
Display and delete duplicate records
**********************************************/

select * from tDupData

EXEC GetDuplicates 'tDupData', 'strCompanyName, strAddress'
EXEC DeleteDuplicates 'tDupData', 'strCompanyName, strAddress'

select * from tDupData

/**********************************************
Clean up the database
**********************************************/
IF OBJECT_ID('tDupData') IS NOT NULL
DROP TABLE tDupData
GO