SQL Server:主键重复错误 [英] SQL Server : Duplicate Primary Key error

问题描述

我正在使用SQL Server Express并通过导入向导"进行导入，因此没有提供任何代码.

I am using SQL Server Express and importing via the Import Wizard so no code to offer really.

错误显示在这里:

错误0xc0202009:数据流任务1:SSIS错误代码DTS_E_OLEDBERROR.
发生OLE DB错误.错误代码:0x80004005.
OLE DB记录可用.源:"Microsoft SQL Server本机客户端11.0"结果:0x80004005说明:该语句已终止.

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.".

一个OLE DB记录可用.
来源:"Microsoft SQL Server本机客户端11.0"
结果:0x80004005
描述:违反了PRIMARY KEY约束'PK_CXS'.无法在对象'dbo.cxs'中插入重复的键.重复的键值为(00，00，000000，2017，03，01，00000000，0).".

An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 11.0"
Hresult: 0x80004005
Description: "Violation of PRIMARY KEY constraint 'PK_CXS'. Cannot insert duplicate key in object 'dbo.cxs'. The duplicate key value is (00, 00, 000000, 2017, 03, 01, 00000000, 0).".

此后，我已经通过按给定的值进行过滤在Excel中进行了测试.只有一行出现.我将代表主键的八列串联起来，然后查找重复项.没有发现.接下来，使用记事本++中的TextFX工具，我尝试删除重复项，但没有发现重复项.

I have since done tests in Excel by filtering by the values given. Only one row comes up. I concatenated the eight columns that represent the primary key and then look for duplicates. None are found. Next, using TextFX tools in Notepad ++, I tried to remove duplicates and none were found.

还有其他方法可以找到似乎不存在的重复项吗?

Any other methods for finding this seemingly nonexistent duplicate?

推荐答案

一些建议(这并不是一个完整的答案，但是根据给定的数据，据我所知).

A few suggestions (that are not really a full answer, but with the given data, is as far as I can go).

在某些情况下，您的文本文件中的文本数据(例如1.000000001和1.000000002)可能会不同，但是一旦转换为文本数据，它们就会变得相同目标类型，因为它会被截断，舍入或其他转换效果.这可能就是为什么您无法在文本文件中找到重复项，而数据库却找到了重复项的原因.

In some occasions, you may have data as text (such as 1.000000001 and 1.000000002) that are different in your text files, but become the same once converted into their destination types, because of truncation, rounding, or other kind of conversion effect. This might be the reason why you cannot find duplicates in your text file, yet the database finds them.

您可以使用和检查的技术很少:

There are few techniques you could use and check:

1. 除了导入的数据外……目标表上是否有任何以前的数据 ?如果是这样，那么您可能会有重复的来源.

1. Apart from the imported data ... is there any previous data already on the target table? If so, there you have a possible source for duplicates.

您是否有任何方式(分批)导入行"，从而可以帮助您找到有问题的行?

Do you have any way to import "row by row" (or in batches) so that this can help you locate the offending one?

如果以前的替代方法均不可行，则可以按照以下过程进行操作:

If none of the previous alternatives are possible, you can follow this process:

• 创建一个与您的 target 具有相同结构的表，但没有任何PRIMARY KEY或UNIQUE约束.我们称之为load_table

• Create a table with the same structure as your target one, but without any PRIMARY KEY or UNIQUE constraints. Let's call it load_table

将数据导入此表.它不应该抱怨PRIMARY KEY约束，因为没有约束.

Import your data to this table. It should not complaint of PRIMARY KEY constraints because there isn't any.

执行以下查询以查找重复项:

Perform the following query to find out duplicates:

SELECT 
    k1, k2, k3 ... kn 
FROM 
    load_table
GROUP BY
    k1, k2, k3 ... kn
HAVING
    count(*) > 1

其中k1，k2，k3 ... kn是构成 target 表主键的所有列.

where k1, k2, k3 ... kn are all the columns that would comprise the primary key of your target table.

使用这些技术，您将找到SQL Server找到的重复项，但是可以避免使用直至现在的方法.

Using these techniques, you will find the duplicates that SQL Server finds but elude the methods you've used up-until-now.

这篇关于SQL Server:主键重复错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！