SQL Server传输数据时生成重复行

本文介绍了SQL Server传输数据时生成重复行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试使用AdventureWorks数据库为另一个数据库生成一些测试数据.当我执行以下查询时，它将产生24个重复的主键.因此，除非关闭目标数据库表上的PK，否则查询将不会运行.源数据库不包含这些重复项.

I''m trying to use the AdventureWorks database to generate some test data for another database. When I execute the following query it produces 24 duplicate primary keys. So the query will not run unless I turn off the PK on the target db table. The source db does not contain these duplicates.

INSERT INTO [TestDb] (TestId, Name, Location)
SELECT
  p.[BusinessEntityID],
  p.[FirstName]+ISNULL(p.[MiddleName], '')+p.[LastName],
  a.[City]
FROM [AdventureWorks2008R2].[Person].[Person] AS p
INNER JOIN [AdventureWorks2008R2].[Person].[BusinessEntityAddress] AS b
ON p.BusinessEntityID = b.BusinessEntityID
INNER JOIN [AdventureWorks2008R2].[Person].[Address] AS a
ON a.AddressID = b.AddressID;

关于为什么要创建重复项的任何建议?有没有更好的方法来生成测试数据?如果我不在乎丢失信息，那么删除重复行的最佳方法是什么?问候，Dave

Any suggestion as to why the duplicates are being created? Is there a better way to generate test data? What is the best way to delete duplicate rows, given that I don''t care if I lose information? Regards, Dave

推荐答案

BusinessEntityID  (No column name)  City
2996                  AmandaSCook         Everett
2996                  AmandaSCook         Everett
2997                  AmandaLMorgan         San Gabriel

在此示例中，AmandaSCook在BusinessEntityAddress中具有两个条目.
但是您仍然将Person.BusinessEntityID作为要插入TestDB的键.那就是您重复和PK违规的来源.

如果您只需要testdata而不关心这些信息，请尝试以下操作:

In this example AmandaSCook has two entries in BusinessEntityAddress.
But you still take Person.BusinessEntityID as key to insert into TestDB. That''s where your duplicates and PK violations come from.

Given you just want testdata and don''t care for the information, try this:

select
  p.[BusinessEntityID],
  p.[FirstName]+ISNULL(p.[MiddleName], '')+p.[LastName],
  a.[City]
FROM [AdventureWorks2008R2].[Person].[Person] AS p
INNER JOIN [AdventureWorks2008R2].[Person].[BusinessEntityAddress] AS b
ON p.BusinessEntityID = b.BusinessEntityID
INNER JOIN [AdventureWorks2008R2].[Person].[Address] AS a
ON a.AddressID = b.AddressID
group by  p.[BusinessEntityID],
  p.[FirstName]+ISNULL(p.[MiddleName], '')+p.[LastName],
  a.[City]
having count(*) = 1

这篇关于SQL Server传输数据时生成重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！