Используйте T-SQL Merge для обновления существующих записей и вставки несуществующих записей, но избегайте дублирования.

У меня есть две таблицы с идентичной структурой t1 и t2.
Таблица t1 имеет примерно на 100+ записей больше, чем t2.

Вот небольшой образец t1.

| pid   | tid    | amt         | paymentdt  | paymentmnth   | startdate                 | enddate                   | updtby
| 670   | 1      | 690.00      | 2015-07-07 | 2015-07-07    | 2015-10-26 14:36:27.000   | 2015-10-26 15:42:42.000   | NULL
| 670   | 11     | 855.00      | 2015-07-07 | 2015-07-07    | 2015-10-26 14:36:27.000   | NULL                      | NULL
| 670   | 13     | 129.00      | 2015-07-29 | 2015-07-29    | 2015-10-26 14:36:27.000   | NULL                      | NULL
| 670   | 2      | 855.00      | 2015-09-01 | 2015-09-01    | 2015-10-26 15:42:42.000   | NULL                      | NULL
| Z41   | 1      | 62.35       | 2015-05-08 | 2015-05-08    | 2015-10-26 10:15:24.000   | 2015-10-26 13:08:05.000   | NULL
| Z41   | 11     | 800.00      | 2015-05-08 | 2015-05-08    | 2015-10-26 10:15:24.000   | NULL                      | NULL
| Z41   | 2      | 298.00      | 2015-06-01 | 2015-06-01    | 2015-10-26 13:08:05.000   | 2015-10-26 14:36:27.000   | NULL
| Z41   | 3      | 298.00      | 2015-07-01 | 2015-07-01    | 2015-10-26 14:36:27.000   | 2015-10-26 15:15:45.000   | NULL
| Z41   | 4      | 298.00      | 2015-08-01 | 2015-08-01    | 2015-10-26 15:15:45.000   | 2015-10-26 15:42:42.000   | NULL
| Z41   | 5      | 238.00      | 2015-09-01 | 2015-09-01    | 2015-10-26 15:42:42.000   | NULL                      | NULL

И небольшой образец t2.

| pid   | tid    | amt         | paymentdt   | paymentmnt   | startdate                 | enddate                   | updtby
| 670   | 1      | 690.00      | 2015-07-07  | 2015-07-07   | 2015-10-02 16:10:50.000   | 2015-10-02 16:35:50.000   | NULL  
| 670   | 11     | 855.00      | 2015-07-07  | 2015-07-07   | 2015-10-02 16:10:50.000   | NULL                      | NULL  
| 670   | 13     | 129.00      | 2015-07-29  | 2015-07-29   | 2015-10-02 16:10:50.000   | NULL                      | NULL  
| 670   | 2      | 855.00      | 2015-09-01  | 2015-09-01   | 2015-10-02 16:35:50.000   | NULL                      | NULL  
| Z41   | 1      | 298.00      | 2015-07-01  | 2015-07-01   | 2015-10-02 16:10:50.000   | 2015-10-02 16:23:26.000   | NULL  
| Z41   | 11     | 800.00      | 2015-05-08  | 2015-05-08   | 2015-10-02 16:10:50.000   | NULL                      | NULL  
| Z41   | 2      | 298.00      | 2015-08-01  | 2015-08-01   | 2015-10-02 16:23:26.000   | 2015-10-02 16:35:50.000   | NULL  
| Z41   | 3      | 238.00      | 2015-09-01  | 2015-09-01   | 2015-10-02 16:35:50.000   | NULL                      | NULL  
| 173   | 1      | 785.00      | 2015-07-01  | 2015-07-01   | 2015-10-02 16:16:30.000   | 2015-10-02 16:27:36.000   | NULL  
| 173   | 11     | 465.00      | 2015-05-01  | 2015-05-01   | 2015-10-02 16:16:30.000   | NULL                      | NULL

Теперь сравнение t1 и t2 показывает, что в t1 есть больше значений для pid Z41, например, tid включают 1, 2, 3, 4 5 и 11. Но в t2 существуют только 1, 2, 3 и 11.

Однако даты начала полностью различаются между t1 и t2, так что это сильно мешает. Ниже показано слияние, которое я пробовал, но в основном он просто вставляет каждую строку с датой начала, отличной от даты t1 в t2.

MERGE INTO t2 AS tgt
USING t1 AS src
    ON tgt.pid = src.pid AND
       tgt.tid = src.tid AND
       tgt.paymentdt = src.paymentdt AND
       tgt.paymentmnt = src.paymentmnt AND
       tgt.startdate = src.startdate
WHEN MATCHED THEN
    UPDATE SET
        tgt.amt = src.amt,
        tgt.paymentdt = src.paymentdt,
        tgt.updatedby = 'MERGEDUPDATE'
WHEN NOT MATCHED THEN
    INSERT (pid, tid, amt, paymentdt, paymentmnt, startdate, enddate, updtby)
    VALUES (src.pid, src.tid, src.amt, src.paymentdt, src.paymentmnt, src.startdate, src.enddate, 'MERGEDINSERT');

С этим слиянием у меня остались дубликаты pid and tid, где в столбце updtby написано «MERGEDINSERT». Но я хочу избежать дублирования.

Как мне правильно выполнить это слияние, чтобы не создавать дубликатов, а вставлять строки, которые существуют в t1, но не в t2, а также обновлять значения amt, paymentdt, and paymentmnth, сохраняя startdate?


merge tsql sql-insert sql-update sql-merge
person gh0st    schedule 27.10.2015    source источник
comment
Можно ли убрать даты и сумму из пункта ON? Каков желаемый результат для данных, которые вы показали?   -  person Giorgi Nakeuri    schedule 27.10.2015

Ответы (2)


arrow_upward
0
arrow_downward

Как вы это описали, ваши критерии слияния должны основываться только на pid и tid. попробуй это

MERGE INTO t2 AS tgt
USING t1 AS src
    ON tgt.pid = src.pid AND
       tgt.tid = src.tid 

WHEN MATCHED THEN
    UPDATE SET
        tgt.amt = src.amt,
        tgt.paymentdt = src.paymentdt,
        tgt.paymentmnth  = src.paymentmnth, 
        tgt.updatedby = 'MERGEDUPDATE'
WHEN NOT MATCHED THEN
    INSERT (pid, tid, amt, paymentdt, paymentmnt, startdate, enddate, updtby)
    VALUES (src.pid, src.tid, src.amt, src.paymentdt, src.paymentmnt, src.startdate, src.enddate, 'MERGEDINSERT');
person ClearLogic    schedule 27.10.2015
comment
Если оставить только pid и tid в предложении on, получится следующее: The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. - person gh0st; 28.10.2015
comment
@ gh0st Это означает, что вы получаете несколько совпадений для pid/tid комбо. Какой желаемый результат? Можете ли вы использовать MAX () для дат? - person Sam cd; 28.10.2015
comment
По сути, я хочу обновить любые записи в t2 соответствующими записями в t1, и если комбинированная запись pid + tid не существует, я хочу ее вставить. - person gh0st; 28.10.2015
comment
Но ваша ошибка заключается в том, что некоторые записи pid + tid существуют более одного раза, поэтому как узнать, какие значения использовать в обновлении? - person Sam cd; 28.10.2015
comment
Возьмем для примера запись pid = Z41, tid = 3 в t2. Я хочу обновить эту запись, чтобы она соответствовала тому, что находится в t1, чтобы для startdate и enddate были установлены соответствующие значения в t1. Затем для записей (pid = Z41, tid = 4) and (pid = Z41, tid = 5) я хочу вставить эти записи в t2. - person gh0st; 28.10.2015
comment
@ gh0st Ошибка заключается в том, что, используя ваш пример, существует более одной записи с pid = Z41, tid = 4 - person Sam cd; 28.10.2015

arrow_upward
0
arrow_downward

Если вы MERGE выдает ошибку из-за получения нескольких записей / совпадений, вы можете ограничить исходную таблицу, используя агрегаты внутри подзапроса, например:

;WITH t1cte AS (
    select pid, tid, amt, paymentdt, paymentmnt, startdate, enddate
    from t1 a
      inner join (select pid,tid,MAX(paymentdt) as maxdt from t1 group by pid,tid) b
        on a.pid = b.pid and a.tid = b.tid and a.paymentdt = b.maxdt
        )

MERGE INTO t2 AS tgt
USING t1cte AS src
    ON tgt.pid = src.pid AND
       tgt.tid = src.tid AND
       tgt.paymentdt = src.paymentdt AND
       tgt.paymentmnt = src.paymentmnt AND
       tgt.startdate = src.startdate
WHEN MATCHED THEN
    UPDATE SET
        tgt.amt = src.amt,
        tgt.paymentdt = src.paymentdt,
        tgt.updatedby = 'MERGEDUPDATE'
WHEN NOT MATCHED THEN
    INSERT (pid, tid, amt, paymentdt, paymentmnt, startdate, enddate, updtby)
    VALUES (src.pid, src.tid, src.amt, src.paymentdt, src.paymentmnt, src.startdate, src.enddate, 'MERGEDINSERT');
person Sam cd    schedule 27.10.2015
comment
Поменял select в inner join() на select pid, tid, max(paymentdt) as maxdt from t1 group by pid, tid. - person gh0st; 28.10.2015
comment
Однако по-прежнему вставляются дубликаты, потому что на tgt.startdate = src.startdate нет совпадений. - person gh0st; 28.10.2015