SQL Server批量插入跳过主键冲突。

Question

我目前使用SQL Server的本机Bulk Insert程序，从文本文件向数据库中加载多个表。

这一方法已经运作得很好，甚至可以很好地处理截断、空值和格式错误。如果某个列太大、应该为 null 的地方不是 null，或者根据格式文件找到的列数不正确，则会跳过这些行并将其转发到错误文件中。

然而，最近在我的一个文本文件中输入了重复的主键。这导致批量插入失败，没有任何行被转发到错误文件中。

目前，我的批量插入命令如下：

BULK INSERT table.table FROM 'data_file.txt' WITH(
    BATCHSIZE = 100000,
    CODEPAGE = 1252,
    DATAFILETYPE = 'char',
    FIRSTROW = 2,
    KEEPNULLS,
    MAXERRORS = 1000000000,
    TABLOCK,
    FORMATFILE = 'format_file.txt',
    ERRORFILE = 'error_file.txt'
)

使用批量插入，有没有一种处理主键冲突的方法与处理其他错误相同？理想情况下，我希望包含主键冲突的行被转发到同一个错误文件中。

- Night Train

Answer 1

直接回答你的问题，不行。

UNIQUE 和 PRIMARY KEY 约束 总是被执行。当导入到一个定义了 NOT NULL 约束的字符列时，BULK INSERT 在文本文件中没有值时插入一个空字符串。

我建议您使用临时表或暂存表进行批量插入，然后再将数据移动到实际表中。

使用 BULK INSERT 时，如果没有使用 CHECK_CONSTRAINTS 选项，还会对表产生其他影响：

CHECK_CONSTRAINTS

指定在批量导入操作期间必须检查目标表或视图上的所有约束。如果没有 CHECK_CONSTRAINTS 选项，则会忽略任何 CHECK 和 FOREIGN KEY 约束，并且操作后，表上的约束将被标记为不可信。

如果您在操作后没有修复它，这可能会对查询性能产生负面影响。

- SqlZim

---

我在 MSDN 文档中看到过这个引用，但不确定是否有独特的解决方法。我没有看到任何人直接问这个问题，所以希望如果其他人在同样的情况下发现自己时，这可以帮助他们。检查约束是我计划启用的内容，感谢您的建议。 - Night Train

这是一个合理的问题，还有其他方法可以实现相同的目标，但据我所知不能使用“BULK INSERT”。祝@NightTrain好运！ - SqlZim

在任何情况下，我都不会直接将数据导入到生产表中。我们不仅使用一个暂存表，而是使用两个。一个包含从文件中获取的所有数据，另一个包含将要进入生产环境的转换后的数据。这使得研究由数据导入引起的问题变得更加容易（我们可以通过一次查询知道麻烦数据是来自我们的转换还是客户端），特别是当你有数百万行的文件时，你不能轻松地打开和搜索。 - HLGEM

我完全同意你的观点。我插入的表格实际上就是文件中未经转换的数据。我试图动态过滤掉错误行，以便能够快速向文件提供者报告。 - Night Train

如果是这种情况，我会从表中删除主键，并编写一个查询来检查重复项，同时使用错误文件查找数据问题。@NightTrain - SqlZim

这是当前的行动计划。谢谢大家。@SqlZim - Night Train