When you are populating the record set, take out the GO between the next-to-the-last and the last insert statements. Having this penultimate GO in the set of queries will remove the scope of the @NOW variable and cause the last INSERT to fail.

If only duplicates need to be removed the ROW_NUMBER() may not be needed.

WITH cteEmployeeOrderedByMyRank AS(SELECT RANK() OVER (PARTITION BY EMPID,FNAME,LNAME ORDER BY REFDATE ASC) AS PartitionRank, * FROM EMPLOYEE -- WHERE 1 = 1)DELETE FROM cteEmployeeOrderedByMyRankWHERE PartitionRank > 1 ;

Interesting demonstration of the ROW_NUMBER() function. Please say it ain’t so, Joe! - that you are not using cursors to remove duplicate rows. Even the technique of a SELECT DISTINCT into a temporary table would be a better option. As other readers have commented, there are a number of ways to remove duplicate rows. This would be my approach:

DELETE Employee

FROM Employee a INNER JOIN (SELECT Empid,

FName,

LName,

MIN(RefDate) AS 'MinDate'

FROM Employee

GROUP BY Empid, FName, LName) b

ON a.Empid = b.Empid

AND a.FName = b.FName

AND a.LName = b.LName

AND a.RefDate > b.MinDate

This would still leave the issue of James verses Jim that would need to be resolved separately. If you didn’t care about spelling variations and wanted to assume that the first entry was the correct one then this would work:

DELETE Employee

FROM Employee a INNER JOIN (SELECT Empid,

MIN(RefDate) AS 'MinDate'

FROM Employee

GROUP BY Empid) b

ON a.Empid = b.Empid

AND a.RefDate > b.MinDate

I would be interested in the question of performance between the two techniques but I’d put my money on mine which I suspect has a whole lot less overhead even as a cross join than having the engine generate a row position.

Another option (that works with SQL 2000) and even in cases of all the columns having the same value (no column to differentiate the rows), is inserting the result set into a new table with an identity column (or adding an identity column to the original table). After that, it's just a matter of keeping the distinct rows as shown in the comments.

I can't remember where I read this solution, but it was either here in SQL Server Central or SQL Team foruns.