插入时跳过/忽略重复行 [英] Skip-over/ignore duplicate rows on insert

问题描述

我有以下表格:

DataValue

DateStamp    ItemId   Value
----------   ------   -----
2012-05-22   1        6541
2012-05-22   2        12321
2012-05-21   3        32

tmp_holding_DataValue

DateStamp    ItemId   Value
----------   ------   -----
2012-05-22   1        6541
2012-05-22   4        87
2012-05-21   5        234

DateStamp 和 ItemId 是主键列.

我正在做一个全天定期运行的插入(在存储过程中):

I'm doing an insert which runs periodically throughout the day (in a stored procedure):

insert into DataValue(DateStamp, ItemId, Value)
select DateStamp, ItemId, Value from tmp_holding_DataValue;

这会将数据从保持表 (tmp_holding_DataValue) 移动到主数据表 (DataValue).然后保留表被截断.

This moves data from the holding table (tmp_holding_DataValue) across into the main data table (DataValue). The holding table is then truncated.

问题在于，在示例中，保存表可能包含主表中已存在的项目.由于密钥不允许重复值，因此过程将失败.

The problem is that as in the example, the holding table could contain items which already exist in the main table. Since the key will not allow duplicate values the procedure will fail.

一种选择是在插入过程中放置​​一个 where 子句，但主数据表有 1000 万多行，这可能需要很长时间.

One option would be to put a where clause on the insert proc, but the main data table has 10 million+ rows, and this could take a long time.

有没有其他方法可以让程序在尝试插入时跳过/忽略重复项?

Is there any other way to get the procedure to just skip-over/ignore the duplicates as it tries to insert?

推荐答案

INSERT dbo.DataValue(DateStamp, ItemId, Value)
SELECT DateStamp, ItemId, Value 
FROM dbo.tmp_holding_DataValue AS t
WHERE NOT EXISTS (SELECT 1 FROM dbo.DataValue AS d
WHERE DateStamp = t.DateStamp
AND ItemId = t.ItemId);

这篇关于插入时跳过/忽略重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！