Click here to Skip to main content
15,882,063 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi,
How to delete duplicate records in sql server.

Example:
Name   Town    Percent
Naga   Hyd       87%
Naga   Hyd       87%
Raj    Vij       44%
Raj    Vij       44%

Result is
Naga Hyd 87%
Raj  Vij 44%

Without selecting the column name I need delete query, because I will pass table name dynamically in stored procedure. I dont know column names dynamically passed table name.
There is no primary key column in tables.

Help me.
Thanks
Posted
Comments
F-ES Sitecore 20-Jul-15 6:27am    
What you're doing is a bad idea, but google "ms sql server execute dynamic sql" and you'll find examples of using "EXEC", so build the relevant sql and execute it dynamically.
NagaRaju Pesarlanka 20-Jul-15 6:38am    
I want to delete duplicate records from 85 tables and those table having different columns. I know exec to run dynamic queries. but I want to pass only table name to stored procedure to delete duplicate records.

 
Share this answer
 
SQL
select distinct * into newtablename from oldtablename

Now, the newtablename will have no duplicate records.

Simply change the table name(newtablename) by pressing F2 in object explorer in sql server.
 
Share this answer
 
If you need to delete the rows from the table and you can't create a new table, you need to know the columns in order to create a condition defining what is duplicate.

Having that said, it is enough that you pass the name of the table to the procedure. You can get rest of the information from sys.columns[^] system view. This enables you to create a SQL statement dynamically based on the table name and then execute the dynamic statement.

So the basic idea for the procedure would be
- get the table name as a parameter
- fetch the names of the columns in that table
- build the DELETE statement into a NVARCHAR variable
- execute the statement using sp_executesql[^]

For the DELETE statement, there are a lot of ways to differentiate the duplicate records in a table. One way is to use the physical location as described in How to remove duplicate rows in SQL Server 2008 when no key is present[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900