SQL SERVER – 2000 – SQL SERVER – Delete Duplicate Records – Rows – Readers Contribution

I am proud on readers of this blog. One of the reader asked asked question on article SQL SERVER – Delete Duplicate Records – Rows and another reader followed up with nice quick answer. Let us read them both together.

Question from Mayank Mishra

Is it possible to delete a single column from a table in SQL 2000.

Thanks

Answer from Ray McCoy

This works in 2000. WARNING: According to MS, SET ROWCOUNT will not work with INSERT, DELETE, and UPDATE in later versions.

-- Create table with a number of values between zero and nine
SELECT a+b+c AS val
INTO dbo.rmtemp
FROM (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a
, (SELECT 0 b UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) b
, (SELECT 0 c UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) c
-- Show what you’ve got
SELECT val,COUNT(*) row_count FROM dbo.rmtemp GROUP BY val
-- Limit processing to a single row
SET ROWCOUNT 1
-- While you’ve got duplicates, delete a row
WHILE (SELECT TOP 1 val FROM dbo.rmtemp GROUP BY val HAVING COUNT(*) > 1) IS NOT NULL
DELETE FROM dbo.rmtemp WHERE val IN (SELECT TOP 1 val FROM dbo.rmtemp GROUP BY val HAVING COUNT(*) > 1);
-- Remove single row processing limit
SET ROWCOUNT 0
-- Confirm that only uniques remain
SELECT val,COUNT(*) row_count FROM dbo.rmtemp GROUP BY val
-- Clean up
DROP TABLE dbo.rmtemp
GO

– Comment
I knew there had to be a better way.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

7 thoughts on “SQL SERVER – 2000 – SQL SERVER – Delete Duplicate Records – Rows – Readers Contribution

  1. HI pinaldave ,
    i have one query how to delete image duplicate row in my table. my table has no primary key or identity column .. if u have any solution then please help me …

    thanks
    kuldeep.

    Like

  2. i need an answer to following question if can someone help me out with this

    How would you complete the following string, in order to obtain a value equal to
    2 + the current nesting level?
    CREATE PROC usp_NestLevelValues AS
    SELECT @@NESTLEVEL AS ‘Current Nest Level';
    EXEC __________ N’SELECT @@NESTLEVEL as TwoGreater’ ;
    GO
    EXEC usp_NestLevelValues;
    GO

    Like

  3. This is your answer
    CREATE PROC usp_NestLevelValues AS
    SELECT @@NESTLEVEL AS ‘Current Nest Level';
    EXEC sp_executesql N’SELECT @@NESTLEVEL as TwoGreater’ ;
    GO
    EXEC usp_NestLevelValues;
    GO

    Like

  4. Hi,
    Creating table copy with unique clustered index on ID column, and coping
    original table rows to table copy sounds reasonable , specially for large tables. It works fast !!! using SQL Server in-built unique clustred key constraint with eliminate duplicates with IGNORE_DUP_KEY option enabled.

    For instance:

    CREATE TABLE Data_backup….. (the same structure as original table Data with duplicates)

    (SQL Server 2000)
    CREATE UNIQUE CLUSTERED INDEX PK_Data_backup
    ON Data_backup(ID)
    WITH IGNORE_DUP_KEY, FILLFACTOR = 90
    GO

    SET IDENTITY_INSERT Data_backup ON
    INSERT INTO Dane_backup(ID,data1,data2)
    SELECT * from Data
    SET IDENTITY_INSERT Data_backup OFF

    Finally you will get table Data_backup with duplicates removed automatically by unique clustered key contraint mechanism.

    Krzsiek. IT WORKS FAST FOR LARGE TABLES.

    :)

    Like

  5. Pingback: SQL SERVER – Select and Delete Duplicate Records – SQL in Sixty Seconds #036 – Video « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s