I have a fairly large table in production with almost a million records, and I have duplicate records in that table with about 18,000 duplicate records. I've been trying some sql suggestions to delete duplicates, and I found your example here I was able to (I think) incorporate my field information into. Running this statement against my test database which is only half my production database, i executed the query which is running for some 4+ hours. When i stopped the query from executing, i found i had some 38,000+ records added to my database table... so I'm wondering if i made some error when configuring your sql into my database. Here is the code i created using your example.
SET ROWCOUNT 1
WHILE @@rowcount > 0
DELETE pb FROM Expense as pb
(SELECT accountCode, expenseDescription, invoiceDate, invoiceNumber, ledgerCode, openItemNumber,
programCode, transactionAmount, vendorName, warrantNumber, lineNumber, collocationCodeID
where expenseDescription like 'PP%'
GROUP BY accountCode, expenseDescription, invoiceDate, invoiceNumber, ledgerCode, openItemNumber,
programCode, transactionAmount, vendorName, warrantNumber, lineNumber, collocationCodeID HAVING count(*) > 1)
AS c ON c.accountCode = pb.accountCode
and c.expenseDescription = pb.expenseDescription
and c.invoiceDate = pb.invoiceDate
and c.invoiceNumber = pb.invoiceNumber
and c.ledgerCode = pb.ledgerCode
and c.openItemNumber = pb.openItemNumber
and c.programCode = pb.programCode
and c.transactionAmount = pb.transactionAmount
and c.vendorName = pb.vendorName
and c.warrantNumber = pb.warrantNumber
and c.lineNumber = pb.lineNumber
and c.collocationCodeID = pb.collocationCodeID
SET ROWCOUNT 0
SELECT * FROM Expense
DROP TABLE Expense
So, my questions are... Will this sql actually delete what seems like temp records? With this size table, should i expect this sql to execute for hours???
I would appreciate any insight, this is my first time performing a task like this.