Prev: What does Profiler/Perf Monitor tick marks represent?
Next: Ceckpoints in SQL 2005
From: SQLDeveloper-Sonia on 11 Aug 2010 14:38
Hi,

PFB a simpler way to delete Duplicate data from a table

USE SAMPLE
GO
CREATE TABLE #TAB1
(ID INT,
NAME VARCHAR(10)
)

INSERT INTO #TAB1
SELECT 1, 'JOHN'

INSERT INTO #TAB1
SELECT 1, 'JOHN'


INSERT INTO #TAB1
SELECT 2, 'Stephen'

INSERT INTO #TAB1
SELECT 3, 'Sonia'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

INSERT INTO #TAB1
SELECT 4, 'RIYA'

--SELECT * FROM #TAB1

--To Identify Duplicates
SELECT * FROM #TAB1
GROUP BY ID, NAME HAVING COUNT(*) > 1


--To Delete Duplicates from the Table

DECLARE @DupCount INT
DECLARE @NonDupCount INT
DECLARE @TotalCount INT
DECLARE @NAME VARCHAR(10)
DECLARE @ID INT

SELECT @TotalCount = COUNT(*) FROM #TAB1

SELECT * FROM #TAB1
GROUP BY ID, NAME HAVING COUNT(*) = 1
SELECT distinct ID, name FROM #TAB1

SET @NonDupCount = @@ROWCOUNT

SELECT @DupCount = @TotalCount - @NonDupCount
--Select distinct * from #TAB1

Select @DupCount as dup, @TotalCount as total, @NonDupCount as nondup

set @DupCount=(a)DupCount+4
SET ROWCOUNT 1

WHILE @DupCount > 0
BEGIN
--SELECT TOP 1 NAME , ID FROM #TAB1 GROUP BY ID, NAME HAVING COUNT(*) > 1
SELECT TOP 1 @NAME = NAME , @ID = ID FROM #TAB1 GROUP BY ID, NAME HAVING
COUNT(*) > 1
--Select @name as Name , @id as ID

DELETE FROM #TAB1 WHERE NAME = @NAME AND ID = @ID
Select @name =null, @id =0
SET @DupCount = @DupCount - 1
END
SET ROWCOUNT 0
Select * from #TAB1


DROP TABLE #TAB1



"John Bell" wrote:

> On Mon, 26 Apr 2010 08:38:26 +0300, "Uri Dimant" <urid(a)iscar.co.il>
> wrote:
>
> >Hi John
> >The OP stated that he is using SQL Server 2000 thus I did not mention CTE
> >solution
> >
> I missed that!
>
> John
> .
>
 | 
Pages: 1
Prev: What does Profiler/Perf Monitor tick marks represent?
Next: Ceckpoints in SQL 2005