Sql-server – Delete duplicates from a table with all the columns duplicated

sql-server-2012t-sql

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

Best Answer

If you can, add an UNIQUE INDEX (or PRIMARY KEY) for [ID].

And you could change:

DELETE FROM [dbo].[monthly_prd04_ftp_list]
GO

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.

Related Solutions

Sql-server – Why does OBJECT_ID return NULL in a computed column

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054

How to Generate Table Create Script in T-SQL

Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.

However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:

CREATE TABLE dbo.whatcha
(
  id INT IDENTITY(1,1), 
  x VARCHAR(MAX), 
  b DECIMAL(10,2), 
  y SYSNAME
);

The following script uses the new sys.dm_exec_describe_first_results_set dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No entirely :-)).

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + name + ' ' + system_type_name
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'

DECLARE @dbs TABLE(db SYSNAME);

INSERT @dbs VALUES(N'db1'),(N'db2');
  -- SELECT whatever FROM dbo.databases

SELECT @sql += N'
  INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;'
  FROM @dbs;

SET @sql += N'
  SELECT ' + @cols + ' FROM #tmp;';

PRINT @sql;
-- EXEC sp_executesql @sql;

The resulting PRINT output:

CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,2),y nvarchar(128));
  INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename;
  INSERT #tmp SELECT id,x,b,y FROM [db2].dbo.tablename;
  SELECT id,x,b,y FROM #tmp;

When you are confident it's doing what you expect, just uncomment the EXEC.

(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)

Best Answer

Related Solutions

Sql-server – Why does OBJECT_ID return NULL in a computed column

How to Generate Table Create Script in T-SQL

Related Question