在主键冲突错误后继续 [英] Continue after primary key violation error

问题描述

问题是在导入例程期间从暂存表插入数据期间。

The problem is during inserting data from staging table during import routine.

系统是我遗留的一个遗留的，短期的，而我开发的东西更适合我要避免数据传输失败。

The system is a legacy one I inherited, and short term while I develop something more suitable I want to patch things to avoid the data transfer failing.

不幸的是，工具通过另一个应用程序创建一个入口到表，称为CommReceipt。该键称为CR_Key。如果发生这种情况，那么当自动例程运行插入，说1000行，我们需要从已定义的CR_Key值从另一个系统（不是我的系统）导入。

Unfortunately the facility exists via another application to create an entry into table, called CommReceipt. The key is called CR_Key . if this happens then when the auto routine runs to insert, say 1000 rows we need to import from another system (not my system) with CR_Key values already defined, it fails.

我看到它的方式我有几个选择，但所有的建议将感激向前迈进这个问题的最佳解决方案（长期和短期修复）。

The way I see it I have several options, but all suggestions will be appreciated moving forward for the best solution to this issue (both long and short term fixes) .

它是计划的一部分，以消除流氓应用程序中的功能（但这是一个遗留系统，用旧的不相似的语言编写，可能需要一些努力）

It is part of the plan to eliminate functionality in the rogue application (but this is a legacy system, written in legacy unfamilar language and might take a bit of effort)

如何我处理主键违例。我可以继续，报告违例处理后运行数据插入。

How do I deal with the primary key violation. Can I continue, reporting the violation to deal with after running data insert.

UPDATE：主键CR_Key也恰好是一个标识，是否有一种方法可以删除不应存在的行，并使用相同的ID插入行。我假设....我关闭身份，然后在缺少的行中指定唯一的值，是可信的吗？
现在不需要自动递增id，插入例程具有ID

UPDATE: the Primary key CR_Key also happens to be an identity , is there a way to delete rows which should not be there and insert rows using the same ID. I presume....I turn the identity off, then specify unique values in the 'missing rows', is that plausible? I don't need to auto increment id now, theinsert routine has ID's

感谢

推荐答案

您可以使用而不是插入触发器。在触发器内部，执行Insert into the table，where not exists CommReceipt.CR_Key = inserted.CR_Key。

You could use an instead of insert trigger. Inside the trigger, do an Insert into the table, where not exists CommReceipt.CR_Key = inserted.CR_Key.

Create trigger T_CommReceiptInsteadOfInsert on CommReceipt
Instead of Insert
As
Begin

--Insert duplicate records into another table
Insert Into CommReceipt_Duplicates(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

--Insert non duplicate records
Insert Into CommReceipt(CR_Key, ...)
Select CR_Key, ...
From inserted i
Where not exists (select * from CommReceipt c Where c.CR_Key = i.CR_Key)

End

这篇关于在主键冲突错误后继续的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！