Блог

Мне нужно создать хранимую процедуру в SQL Server и реализовать upsert таким образом, чтобы она перемещала данные из промежуточной таблицы (a.k.a Source) в конечную таблицу (a.k.a target) и отмечала строки, которые являются новыми, обновленными, не обновляемыми или удаляемыми каждый раз при новомпоступает пакет данных. Я использую слияние, как описано здесь .

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

1. Загрузка данных в исходную таблицу
2. Вызовите хранимую процедуру, которая переместит данные из Source Target в на основе критериев слияния

Моя хранимая процедура выглядит следующим образом:

 CREATE PROCEDURE [dbo].[upsert_with_flag_2]
AS
    DECLARE @current_time AS datetime
    SET @current_time = GETDATE()

    MERGE [dbo].[employee] AS Target
    USING [dbo].[employee_staging] AS Source
          ON Source.[first_name] = Target.[first_name] 
             AND Source.[last_name] = Target.[last_name] 
             AND Source.[dob] = Target.[dob]

    WHEN MATCHED
        THEN
            UPDATE 
            SET Target.[salary] = Source.[salary],
                Target.[current_address] = Source.[current_address],
                Target.[is_deleted] = 'Updated',
                Target.[processed_date] = @current_time

    WHEN NOT MATCHED BY Target 
        THEN
            INSERT ([first_name], [last_name], 
                    [dob], [salary], 
                    [current_address], [is_deleted], 
                    [processed_date])
            VALUES (Source.[first_name], Source.[last_name],
                    Source.[dob], Source.[salary], 
                    Source.[current_address], 'New',
                    @current_time);

-- After doing upsert, check for rows whose processed date is less than current date but status is new or updated, These are
-- the rows which were not present in input file. Update there status to deleted
-- QUESTION: Should we change the processed date to current date for row's whose status is deleted?

UPDATE [dbo].[employee]
SET [is_deleted] = 'deleted'
WHERE ([is_deleted] = 'New' OR [is_deleted] = 'Updated') 
  AND [processed_date] < @current_time
 

После этого я выполняю следующие шаги для загрузки данных и получения выходных данных:

 --Loading the initial data
TRUNCATE TABLE [dbo].[employee_staging]
GO

INSERT INTO [dbo].[employee_staging] ([first_name], 
                                      [last_name], 
                                      [dob], 
                                      [salary], 
                                      [current_address])
VALUES  ('John', 'Doe', '1995-04-28', 3000, 'Andra Pradesh'),
        ('Robert', 'Spenser', '1994-03-28', 1800, 'Madhya Pradesh'),
        ('Vikash', 'Sharma', '1996-12-20', 1400, 'Uttar Pradesh'),
        ('Anup', 'Soni', '1994-03-28', 1800, 'Delhi'),
        ('Prijan', 'Sonar', '1989-01-28', 3000, 'Himachal Pradesh')
GO

EXEC upsert_with_flag

SELECT * FROM [dbo].[employee]

--Loading the updated data
TRUNCATE TABLE [dbo].[employee_staging]
GO

INSERT INTO [dbo].[employee_staging] ([first_name], [last_name], 
                                       [dob], [salary], 
                                       [current_address])
VALUES ('Robert', 'Spenser', '1994-03-28', 2000, 'Madhya Pradesh'),
       ('Vikash', 'Sharma', '1996-12-20', 1400, 'Maharashtra'),
       ('Anup', 'Soni', '1994-03-28', 1800, 'Delhi'),
       ('Prijan', 'Sonar', '1989-01-28', 3000, 'Himachal Pradesh'),
       ('William', 'Beck', '1991-04-22', 3300, 'Karnataka'),
       ('Robert', 'Brownie', '1986-04-22', 5000, 'Assam')
 

Обратите внимание на строки № 4 и 5. Никаких изменений во входных данных строки для Anup не произошло, тем не менее, я получаю столбец [is_deleted] как «Обновленный». Я хочу, чтобы это было что-то вроде «Существующий» или «Без изменений».

Пожалуйста, помогите сделать это возможным. Эта логика upsert является частью большого конвейера, и нам нужны обновленные, новые, не обновленные или удаленные строки в новом файле. Как мне этого добиться?

Вы можете добавить оператор case в поле is_deleted в обновлении, чтобы проверить, изменилось ли что-нибудь. Что-то вроде этого:

 MERGE [dbo].[employee] as Target
USING [dbo].[employee_stagging] as Source
ON Source.[first_name] = Target.[first_name] and
   Source.[last_name] = Target.[last_name] and
   Source.[dob] = Target.[dob]

WHEN MATCHED
THEN
    UPDATE 
    SET Target.[salary] = Source.[salary],
        Target.[current_address] = Source.[current_address],
        Target.[is_deleted] = CASE WHEN Source.salary = Target.salary
                                         AND Source.current_address = Target.current_address THEN 'No change'
                                   ELSE 'Updated'
                               END,
        Target.[processed_date] = @current_time

WHEN NOT MATCHED BY Target 
THEN
    INSERT ([first_name],
            [last_name],
            [dob],
            [salary], 
            [current_address], 
            [is_deleted],
            [processed_date])

    VALUES (Source.[first_name],
            Source.[last_name],
            Source.[dob],
            Source.[salary], 
            Source.[current_address], 
            'New',
            @current_time
            );
 

Таким образом, если поля обновления идентичны промежуточным данным, он обновит is_deleted на «Без изменений», тогда как, если поля обновления изменились, он обновит is_deleted на «Обновленный».

ПРИМЕЧАНИЕ: В этом коде предполагается, что поля salary и current_address не являются обнуляемыми (за определением таблицы для поля следует NOT NULL). Если есть nullable , то вы должны обработать значения null, заменив оператор CASE следующим:

 CASE WHEN (Source.salary = Target.salary
        OR Source.salary IS NULL
            AND Target.salary IS NULL)
    AND (Source.current_address = Target.current_address
        OR Source.current_address IS NULL
            AND Target.current_address IS NULL) THEN 'No change'
    ELSE 'Updated'
END
 

(Документация здесь)