如何获取重复值 [英] how to get duplicate values

问题描述

大家好，



我们有相同的结构主表和一个包含30列的临时表，我们正在尝试将数据从登台加载到主表。主表有6个主键列，但在加载数据时显示主键违规错误。如何识别重复记录。



谢谢，

Ramesh

解决方案

  SELECT  *  INTO  temp_duplicates  FROM  tbl_master 
 
  INSERT  < span class =code-keyword> INTO  temp_duplicates 
  SELECT  *  FROM  tbl_staging 
 
   -   以识别重复项 
  SELECT  id，name，COUNT（*）
  FROM  tem p_duplicates 
  GROUP   BY  id，name   -  主要的pk列 
  HAVING  COUNT（*）> 1 
  GO  
 
   -   插入非重复记录 
  INSERT   INTO  tbl_master 
  SELECT  *  FROM  temp_duplciates 
  WHERE  < span class =code-keyword> NOT   EXISTS （ SELECT   1   FROM  tbl_master  WHERE  tbl_master.id = temp_duplciates.id  AND  tbl_master.name = temp_duplciate s.name）
 
  DROP   TABLE 重复

已添加代码块[/ Edit]

尝试：

  SELECT  m。*  FROM 主m  JOIN 暂存s < span class =code-keyword> ON  m.Id = s.Id 

首先，尝试删除主键（PK） ）一个接一个。删除第一个PK时，尝试在表上使用 SELECT 语句来检查错误。如果没有帮助，请删除下一个PK，直到成功。



警告： 请小心。删除PK，你可能会丢失数据的完整性!!!



当你成功获取数据时，尝试恢复PK和 - 逐个 - 再次使用 SELECT 语句。通过这种方式，您可以找到引发错误的列;）



我无法给您一个示例查询，因为您没有提供足够的信息; （

Hi All,

We have same structure master table and a staging table with 30 columns, we are trying to load the data from staging to master table.In master table have 6 primary key columns but while loading the data it show primary key violation error.How to identify the duplicate records.

Thanks,
Ramesh

解决方案

SELECT * INTO temp_duplicates FROM tbl_master

INSERT INTO temp_duplicates
SELECT * FROM tbl_staging

--to identify duplicates
SELECT id,name,COUNT(*) 
FROM temp_duplicates
GROUP BY id,name --pk columns in master
HAVING COUNT(*)>1
GO

--insert non duplicate records
INSERT INTO tbl_master
SELECT * FROM temp_duplciates 
WHERE NOT EXISTS(SELECT 1 FROM tbl_master WHERE tbl_master.id = temp_duplciates.id AND tbl_master.name = temp_duplciates.name)

DROP TABLE duplicates

[Edit]Code block added[/Edit]

Try:

SELECT m.* FROM Master m JOIN Staging s ON m.Id = s.Id

First of all, try to delete primary key (PK) one by one. When you remove first PK, try to use SELECT statement on your table to check for errors. If it wasn't helpful, remove next PK, until success.

Warning: Please, be careful. Removing PK's, you can lose integrity of the data!!!

When you successfully fetch data, try to restore PK's and - also one by one - again use SELECT statement. By doing in this way, you can find the column which raises error ;)

I can't give you an example query, because you did not provide enough information ;(

这篇关于如何获取重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！