How to delete duplicate records in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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 20-Jul-15 0:18am

NagaRaju Pesarlanka

Add a Solution

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.

3 solutions

Solution 1

you visit this link . you get a right answer .
http://www.besttechtools.com/articles/article/sql-query-to-delete-duplicate-rows[^]

Posted 20-Jul-15 0:48am

Amresh Bahadur Singh

Solution 2

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.

Posted 20-Jul-15 0:35am

Dharmesh .S. Patil

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2015-07-20T03:07:00

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[^]