Remove duplicates

Question

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

Piyush Pansuriya · Answer

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" );

Ravi Sangtani · Answer

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 Pansuriya · Answer

Hi, R u missing this? command.ExecuteNonQuery();

Piyush Pansuriya · Answer

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);

Uttam Chaturvedi · Answer

I would recommend to do it from sql server Management studio. You can even create a small stored proc to do so.

Tushar Dikshit · Answer

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 Sontakke · Answer

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.

Piyush Pansuriya · Answer

variable connection defined 2 times, Please check your code.

Shankar M · Answer

Another error A local variable named connection is already defined in this scope

Shankar M · Answer

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 ')'.

Shankar M · Answer

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 M · Answer

Hi Piyush Pansuriya, Thanks for your suggestion. code doesn't works same issue