2

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
8
  • How can you have duplicate with an identity column?
    – paparazzo
    Jan 3 '18 at 13:18
  • @Paparazzi Technically, the identity property on its own does nothing to prevent duplicates. Jan 3 '18 at 15:12
  • @AaronBertrand The question is not about prevent duplicates. The solution in the question includes ID.
    – paparazzo
    Jan 3 '18 at 15:15
  • @Paparazzi I was just addressing your question. It might imply to other readers that IDENTITY = no duplicates, which is not true, and is easy to prove. Also can be dangerous to assume that just because a column is called "ID" that it is explicitly defined as unique/PK. Jan 3 '18 at 15:17
  • @AaronBertrand Again back to the question. " there was no Prime Key. The ID column was also an Identity column."
    – paparazzo
    Jan 3 '18 at 15:21
2

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.

1
  • Great catch on TRUNCATE. I should know that one by now.
    – Kennah
    Jan 4 '18 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.