主键重复记录绕过到下一次插入 [英] primary key duplicate record bypass to next insert

问题描述

奇怪的问题.我在表中插入 10,000 条左右的记录，主键不是身份字段.因此，当插入所有 10,000 条数据时，如果有些数据是重复的，有没有办法跳到 sql server insert 中的下一条记录并确保非重复数据进入?我真的不在乎没有插入重复项.

weird question. i am inserting 10,000 records or so in a table and the primary key is not an Identity field. so when inserting all 10,000 if some are duplicate, is there a way to skip to next record in sql server insert and make sure the non-duplicates go in? i really dont care bout the duplicates not getting inserted.

推荐答案

使用忽略重复键"选项.

Use the "Ignore duplicate key" option.

最简单的方法是在 SQL Server Management Studio 中删除主键.

The simplest way to do this is to delete the Primary Key in SQL Server Management Studio.

然后创建一个新的索引，类型为Index"，将 Is Unique 设置为Yes"，并将Ignore Duplicate Keys"设置为Yes".然后插入你的记录.它将插入除重复项之外的所有内容.完成后，您可以删除此索引，然后重新创建主键.

Then create a new Index, of type "Index", set Is Unique to "Yes", and set "Ignore Duplicate Keys" to "Yes". Then insert your records. It will insert them all except the duplicates. When you are done, you can delete this index, and recreate your Primary Key.

如果您需要 TSQL 方法，请参阅 CREATE INDEX 调用中的 IGNORE_DUP_KEY 选项:

If you want a TSQL method, see the IGNORE_DUP_KEY option in the CREATE INDEX call:

创建索引 (Transact-SQL)

另一种方法是在源表和要插入的记录之间使用 LEFT JOIN 和 GROUP BY 子句，仅插入源中不存在的记录.GROUP BY 将消除新记录中的重复项.

Another way would be to use a LEFT JOIN between your source table and the records you are going to insert, and a GROUP BY clause, only inserting records that don't exist in your source. The GROUP BY will eliminate your duplicates in the new records.

这篇关于主键重复记录绕过到下一次插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！