Delete duplicates from a table with all the columns duplicated I needed to remove duplicates from a table where every column was duplicated and there was no Prime Key.  The ID column was also an Identity column.

[It perplexes me why the Identity column wasn't made a PK when the table was created.  I'm going to look at doing that and test to ensure I don't break anything.]

The table isn't huge--just a few hundred rows.  In terms of performance, it doesn't matter if this is the most efficient method.  However, if there is a better way, I'm interested in hearing.

USE DBMGMT
GO

If(OBJECT_ID('tempdb..#Temp1') Is Not Null) Drop Table #Temp1

SELECT DISTINCT
    [ID]
    ,[Load_Name]
    ,[Remote_File_Name]
    ,[Local_File_Name]
    ,[Status]
    ,[Return_Code]
    ,[Group_Name]
    ,[Priority_Level]
    ,[Enabled]
    ,[Table_Name]
INTO #Temp1
FROM [dbo].[monthly_prd04_ftp_list]

SET IDENTITY_INSERT [dbo].[monthly_prd04_ftp_list] ON
GO

DELETE FROM [dbo].[monthly_prd04_ftp_list]
GO

INSERT INTO dbo.monthly_prd04_ftp_list
    (
    [ID]
    , [Load_Name]
    , [Remote_File_Name]
    , [Local_File_Name]
    , [Status]
    , [Return_Code]
    , [Group_Name]
    , [Priority_Level]
    , [Enabled]
    , [Table_Name]
    )
SELECT
    [ID]
    , [Load_Name]
    , [Remote_File_Name]
    , [Local_File_Name]
    , [Status]
    , [Return_Code]
    , [Group_Name]
    , [Priority_Level]
    , [Enabled]
    , [Table_Name]
FROM #Temp1

GO

SET IDENTITY_INSERT [dbo].[monthly_prd04_ftp_list] OFF
GO
If you can, add an UNIQUE INDEX (or PRIMARY KEY) for [ID].

And you could change:

DELETE FROM [dbo].[monthly_prd04_ftp_list]
GO


by

TRUNCATE TABLE [dbo].[monthly_prd04_ftp_list]
GO


It's faster and it will reseed the identity column to 1 (or it initial value.)

If you don't want to use a temporary table, and there isn't FOREIGN KEYS pointing to this table, you could rename it and insert the DISTINCT values to the final table directly. Then you could DROP the old table.