如何删除sql server中的重复记录 [英] How to delete duplicate records in sql server

问题描述

如何删除sql server中的重复记录。



示例：

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

推荐答案

您访问此链接。你得到一个正确的答案。

http：// www.besttechtools.com/articles/article/sql-query-to-delete-duplicate-rows [ ^ ]

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

select distinct * into newtablename from oldtablename

现在，newtablename将没有重复记录。



只需更改通过在sql server中的对象资源管理器中按F2键来表名（newtablename）。

Now, the newtablename will have no duplicate records.

Simply change the table name(newtablename) by pressing F2 in object explorer in sql server.

如果需要从表中删除行而无法创建新表，则需要知道列以创建定义重复内容的条件。



说，将表的名称传递给过程就足够了。您可以从 sys.columns 获取其余信息[ ^ ]系统视图。这使您可以根据表名动态创建SQL语句，然后执行动态语句。



因此，程序的基本思路是

- 将表名作为参数获取

- 获取该表中列的名称

- 将DELETE语句构建到NVARCHAR变量中
- 使用 sp_executesql 执行语句[ ^ ]



对于DELETE语句，有很多方法可以区分表中的重复记录。一种方法是使用如何描述的物理位置在没有密钥时删除SQL Server 2008中的重复行 [ ^ ]

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

这篇关于如何删除sql server中的重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！