Remove duplicates

Shankar M

The below commnand ran successfully when i click the respective button but no changes on the database

SqlCommand command = new SqlCommand("select distinct * into #tmp From bounce delete from bounce insert into bounce select * from #tmp drop table #tmp)", connection);

connection.Close();

MessageBox.Show("Cleared Duplicates");

Note: Table bounce having only one column called email to remove the duplicates I created this button function

12 Replies

Know the answer? Post it — somebody with the same question will find it here.

Uttam Chaturvedi8y ago
I would recommend to do it from sql server Management studio. You can even create a small stored proc to do so.
Tushar Dikshit8y ago
HI,
If it is one time activity as I understand you don't need to do it from front end.

You have one table, one column remove duplicates from SQL by SQL only.
ADO.Net code is not required in this scenario.

Also you can alter table add unique key constraint and apply validation when enterign data into table from fornt end.(If you have such interface).
Prevent duplicate entries once you clean the data, that is long term solution.

Ideally it is not recommended or practised to perfrom such operation from front end. It is data clean up.

Also if you have to do it on further environemnts like production and you have to follow process, it would be easier to give SQL script to a DBA or support person.
Sample queries -
How can you  DELETE DUPLICATE RECORDS?

Using row count to restrict delete only 1 record
set rowcount 1
DELETE FROM EMPLOYEE WHERE EMPID IN (
SELECT EMPID
FROM EMPLOYEE
GROUP BY EMPID,EMPNAME, SALARY
HAVING COUNT(*)>1
)
set rowcount 0
'----------------------------------
Use auto increment primary key "add" if not available in the table, as in given example.
alter table employee
add empidpk int identity (1,1)
Now, perform query on min of auto pk id, group by duplicate check columns - this will give you latest duplicate records
select * from employee where
empidpk not in ( select min(empidpk) from employee
group by EMPID,EMPNAME, SALARY )
Now, delete.
Delete from employee where
empidpk not in ( select min(empidpk) from employee
group by EMPID,EMPNAME, SALARY )
Nitin Sontakke8y ago
I believe you are approaching the problem from wrong direction.

Perform your duplication removal process manually in SQL Server Management Studio just once.

Then apply either primary key or unique key constraint on that table.

Wherever duplicate data is getting added, you will start getting errors there. Implement logic to handle those errors appropriately.

What you are currently doing is not a solution.
Ravi Sangtani8y ago
Hi,

Why are you deleteing data form bounce and again selecting data from #tmp.

I suggest after insert distinct value from bounce to #tmp,

drop bounce and rename to #tmp to bounce...
Piyush Pansuriya8y ago
variable connection defined 2 times,
Please check your code.
Shankar M8y ago
Another error

A local variable named connection is already defined in this scope
Piyush Pansuriya8y ago
you make a mistake in quatation mark

Try this:
SqlCommand command = new SqlCommand("truncate table bounce"), connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Cleared All data");
Shankar M8y ago
Hi,

I tired

SqlCommand command = new SqlCommand("truncate table bounce)", connection);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Cleared All data");

getting an error Incorrect syntax near ')'.
Piyush Pansuriya8y ago
Hi, R u missing this?
command.ExecuteNonQuery();
Shankar M8y ago
Even I tried by simple sql query like

SqlCommand command = new SqlCommand("truncate table bounce)", connection);
MessageBox.Show("Cleared Duplicates");

it doesnt changes in database
Shankar M8y ago
Hi Piyush Pansuriya,

Thanks for your suggestion.

code doesn't works same issue
Piyush Pansuriya8y ago
Hi, Try this :
SqlCommand command = new SqlCommand("WITH TempBonc (duplicateRecCount)
    AS
    (
    SELECT ROW_NUMBER() OVER(PARTITION by email)
    AS duplicateRecCount
    FROM dbo.bounce
    )
    --Now Delete Duplicate Records
    DELETE FROM TempBonc
    WHERE duplicateRecCount > 1 "), connection);