SQL Server 2005 Remove Dups – CTE

No more selecting DISTINCT into a temp table and then inserting back into the cleaned out table. This does it all for you in two statements.
Deleting from the CTE actually changes the underlying table. Be careful how you setup your CTE. You could have some unintended deletes without the right logic
CREATE TABLE #prod(
    Product_Code varchar(10),
    Product_Name varchar(100)
)

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('123','Product_1')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('234','Product_2')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('345','Product_3')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('345','Product_3')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('456','Product_4')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('567','Product_5')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('678','Product_6')

INSERT INTO #prod(Product_Code, Product_Name)
VALUES ('789','Product_7')

SELECT *
FROM #prod;


With Dups as
(
    select *, row_number() over (partition by Product_Code order by Product_Code) as RowNum
    from #prod
)


Delete from Dups where rownum > 1;

--Note duplicate record 345 Product_3 has been removed.
SELECT *
FROM #prod;

Kill the Sql Connection

/*****************************************************************

*** Procedure: sp_KillConnections

*** Usage: sp_KillConnections @dbName = 'Database Name'

*** Description: Drop all connections from a specific database

*** Input: @dbName - REQUIRED - Name of the database

*** Output: Outputs the results of the proccess

******************************************************************/

CREATE PROCEDURE sp_KillConnections

      @dbName VARCHAR(128)

AS

DECLARE @spid varchar(5)

DECLARE @LoginName nvarchar(128)

DECLARE @intErrorCode int

DECLARE @intOk int

DECLARE @intError int

DECLARE @intTotal int

SET @intErrorCode = 0

SET @intOk = 0

SET @intError = 0

SET @intTotal = 0

SELECT @intTotal = Count(sp.spid) FROM master..sysprocesses sp JOIN master..sysdatabases sd ON sp.dbid = sd.dbid WHERE sd.name = @dbName

DECLARE KILL_CONS CURSOR FOR

SELECT CAST(sp.spid as varchar(5)),RTrim(sp.loginame) FROM master..sysprocesses sp JOIN master..sysdatabases sd ON sp.dbid = sd.dbid WHERE sd.name = @dbName

OPEN KILL_CONS

FETCH NEXT FROM KILL_CONS INTO @spid,@LoginName

WHILE @@FETCH_STATUS = 0

BEGIN

      EXEC('Kill '+ @spid + '')

      SELECT @intErrorCode = @@ERROR

      IF @intErrorCode = 0

      BEGIN

            SET @intOk = @intOk + 1

            PRINT 'Process ' + @spid + ' from login ' + @LoginName + ' has been ended.'

      END

      ELSE

      BEGIN

            SET @intError = @intError + 1

            PRINT 'Process ' + @spid + ' from login ' + @LoginName + ' could not be ended.'

      END

      FETCH NEXT FROM KILL_CONS INTO @spid,@LoginName

END

CLOSE KILL_CONS

DEALLOCATE KILL_CONS

PRINT 'Total number of processes from database ' + @dbName + ': ' + CAST(@intTotal as varchar)

PRINT 'Processes ended normally: ' + CAST(@intOk as varchar)

PRINT 'Processes could not be ended: ' + CAST(@intError as varchar)

GO

shan

shan