Вопрос по sql-server-2008, sql-server – Пропускать / игнорировать повторяющиеся строки при вставке

21

У меня есть следующие таблицы:

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 столбцы первичного ключа.

Я делаю вставку, которая запускается периодически в течение дня (в хранимой процедуре):

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

Это перемещает данные из таблицы хранения (tmp_holding_DataValue) в основную таблицу данных (DataValue). Таблица удержания затем усекается.

Проблема заключается в том, что, как в примере, таблица хранения может содержать элементы, которые уже существуют в основной таблице. Поскольку ключ не допускает дублирования значений, процедура завершится ошибкой.

Один из вариантов - поместить предложение where в процедуру вставки, но основная таблица данных содержит более 10 миллионов строк, и это может занять много времени.

Есть ли другой способ заставить процедуру просто пропустить / игнорировать дубликаты при попытке вставить?

Также очень полезно пометить ваш вопрос с минимальнымversion SQL Server вы должны поддерживать. Я не предлагалMERGE решение, потому что изначально я понятия не имел, какую версию вы используете. Aaron Bertrand
Столбец значения не имеет значения, если его значение не учитывается, то, что уже находится в DataValue для этой метки, следует оставить как есть. finoutlook
Что делать, еслиValue столбец в таблице хранения отличается, например, для первого ряда это3253 вместо6541? Это все еще дубликат? Если нет, то хотите ли вы что-то обновить (например, добавить6541 + 3253 в исходной таблице) или просто заменить? Aaron Bertrand
@AaronBertrand - Да, я должен был упомянуть, что использую 2008 (скоро перейду в 2012). Вы бы пошли с слиянием над где в этом случае? finoutlook
Я не уверен, я все еще нахожуMERGE синтаксис пугающий, и я не решаюсь рекомендовать его в целом. Я не уверен, что все ошибки MERGE были исправлены (см.list referenced by Alex K in his answer to this 2012 question). Если вашей ключевой целью является производительность, то вы должны их протестировать и убедиться, что (а) они поступают правильно и (б) вы выбираете тот, который лучше всего работает в вашей среде. Мы не можем предсказать эти ответы ... Aaron Bertrand

Ваш Ответ

4   ответа
16

SQL Server 2008+:

MERGE
INTO    dataValue dv
USING   tmp_holding_DataValue t
ON      t.dateStamp = dv.dateStamp
        AND t.itemId = dv.itemId
WHEN NOT MATCHED THEN
INSERT  (dateStamp, itemId, value)
VALUES  (dateStamp, itemId, value)
/*
WHEN MATCHED THEN
UPDATE
        value = t.value
*/
-- Uncomment above to rewrite duplicates rather than ignore them
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded finoutlook
Error: User Rate Limit Exceeded finoutlook
0

в результате которого возникла одна и та же ошибка повторяющегося ключа, а затем идея состояла в том, чтобы выбрать несколько отличающихся столбцов (первичных) при возврате также других столбцов,проверять:

INSERT INTO DataValue(DateStamp, ItemId, Value)
SELECT DISTINCT DateStamp, ItemId, MAX(Value) AS Value
FROM tmp_holding_DataValue
GROUP BY DateStamp, ItemId

Фактически, цель может быть достигнута и без Distinct, так как агрегатная функция MAX выберет одно значение.

26
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);
Error: User Rate Limit Exceeded finoutlook
Error: User Rate Limit Exceeded finoutlook
Error: User Rate Limit ExceededSELECT TOP 10000Error: User Rate Limit ExceededORDER BYError: User Rate Limit Exceeded
Error: User Rate Limit ExceededDateStampError: User Rate Limit Exceeded
17

выдаст предупреждение о дублировании ключа и продолжит работу. Я не догадываюсь. Я проверял это.

Я обнаружил, что я не могу сделать это SMSS. Пришлось сбросить и пересоздать индекс через скрипт. Но вы можете щелкнуть правой кнопкой мыши по индексу, выбрать удаление и воссоздать, а затем просто изменить Ignore Duplicate Key = Yes. Для меня SMSS не сразу показало изменение.

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PKallowDup]') AND name = N'PK_PKallowDup')
ALTER TABLE [dbo].[PKallowDup] DROP CONSTRAINT [PK_PKallowDup]
GO

USE [test]
GO

/****** Object:  Index [PK_PKallowDup]    Script Date: 05/22/2012 10:23:13 ******/
ALTER TABLE [dbo].[PKallowDup] ADD  CONSTRAINT [PK_PKallowDup] PRIMARY KEY CLUSTERED 
(
    [PK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = ON, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Или я думаю, что вы могли бы использовать внешнее соединение

INSERT dbo.DataValue(DateStamp, ItemId, Value)
SELECT t.DateStamp, t.ItemId, t.Value 
  FROM dbo.tmp_holding_DataValue AS t 
  left join dbo.DataValue AS d
    on d.DateStamp = t.DateStamp
   AND d.ItemId = t.ItemId
 WHERE d.DateStamp is null 
   and d.ItemId    in null
Error: User Rate Limit Exceeded
Error: User Rate Limit ExceededWITH (IGNORE_DUP_KEY = ON);Error: User Rate Limit ExceededIGNORE_DUP_KEYError: User Rate Limit ExceededDuplicate key was ignored.).
Error: User Rate Limit Exceeded
Error: User Rate Limit Exceeded
Error: User Rate Limit ExceededDataValueError: User Rate Limit Exceeded finoutlook

Похожие вопросы