Updating destination table based on year-week and date comparison

Question

I have two tables - source and destination, which are similar. They look like this:

CREATE TABLE [dbo].[srcTable](
    [Id] [INT] NULL,
    [Value] [INT] NULL,
    [QueryDate] [DATE] NULL
) 

CREATE TABLE [dbo].[destTable](
    [Id] [INT] NULL,
    [Value] [INT] NULL,
    [QueryDate] [DATE] NULL
)

I have the following data in these tables:

TRUNCATE TABLE [dbo].[srcTable]
INSERT INTO [dbo].[srcTable]
(
    [Id],
    [Value],
    [QueryDate]
)
SELECT 2, 3, '2023-01-03'
UNION ALL
SELECT 2, 3, '2023-03-03'
UNION ALL
SELECT 3, 4, '2023-03-02'
UNION ALL
SELECT 5, 6, '2023-03-04'
UNION ALL
SELECT 3, 4, '2023-03-17'


TRUNCATE TABLE [dbo].[destTable]
INSERT INTO [dbo].[destTable]
(
    [Id],
    [Value],
    [QueryDate]
)
SELECT 1, 2, '2023-03-03'
UNION ALL
SELECT 1, 2, '2023-03-10'

I want to always have the most recent QueryDate set in the [dbo].[destTable].

If there is a dataset in the [dbo].[srcTable] which matches the YearWeek of a dataset in the [dbo].[destTable], and the QueryDate of the source is equal or greater than the QueryDate of the destination, then the dataset for this YearWeek in the [dbo].[destTable] should be deleted and a new dataset should be inserted in the [dbo].[destTable] from the source table.

If there is no data in [dbo].[destTable] for a specific YearWeek, then the data is inserted in [dbo].[destTable] from the source.

The requirement is to group the data by YEAR-WEEK and also to compare the QueryDate column per week.

So, after running my query based on the data above, the [dbo].[destTable] will look like this:

SELECT 2, 3, '2023-01-03'
UNION ALL
SELECT 5, 6, '2023-03-04'
UNION ALL
SELECT 1, 2, '2023-03-10'
UNION ALL
SELECT 3, 4, '2023-03-17'

I am trying to use a MERGE statement, but I am not sure how to delete the destination and insert at the same time when the data is matched. I tried this:

MERGE [dbo].[destTable] AS T  
USING (SELECT *  FROM [dbo].[srcTable] ) AS S 
ON CAST(YEAR(T.[QueryDate]) AS VARCHAR(4))  +'-' 
        +   CAST(DATEPART(WEEK, T.[QueryDate]) AS VARCHAR(2)) = CAST(YEAR(S.[QueryDate]) AS VARCHAR(4))  +'-' 
        +   CAST(DATEPART(WEEK, S.[QueryDate]) AS VARCHAR(2)) 
    
WHEN MATCHED AND S.[QueryDate] => T.[QueryDate] THEN 
   DELETE
   -- delete from target and insert again


WHEN NOT MATCHED BY TARGET  THEN 
   INSERT ([Id],
            [Value],
            [QueryDate]) 
    
    VALUES (S.[Id],
            S.[Value],
            S.[QueryDate])
;

Another idea is to spell out the logic to multiple deletes and inserts, nested in a loop for each distinct Year-Week in the source, but this seems long and clumsy. Is there a better way to make the MERGE statement work?

Delete + insert is the same as update, so why not just do that? — siggemannen, Mar 21 '23 at 19:12

score 1 · Accepted Answer · answered Mar 22 '23 at 09:37

MERGE cannot both delete and insert, as it cannot do multiple actions per target row. But you can just do an UPDATE instead.

You need to first filter the source data so that you only have one row per week. You can do that in a CTE.

WITH cte AS  (
    SELECT *
    FROM (
      SELECT *,
        ROW_NUMBER() OVER (PARTITION BY DATETRUNC(week, S.QueryDate) ORDER BY S.QueryDate DESC) AS rn
      FROM dbo.srcTable AS S
    ) S
    WHERE S.rn = 1
) 
MERGE dbo.destTable AS T
USING cte AS S
   ON DATETRUNC(week, S.QueryDate) = DATETRUNC(week, T.QueryDate)
WHEN MATCHED AND S.QueryDate >= T.QueryDate THEN
  UPDATE SET
    Id = S.Id,
    Value = S.Value,
    QueryDate = S.QueryDate
WHEN NOT MATCHED THEN 
  INSERT (Id, Value, QueryDate) 
  VALUES (S.Id, S.Value, S.QueryDate)
;

db<>fiddle

Note that I've used DATETRUNC(week to compare by week, this is only available in SQL Server 2022. For older versions you can use a combination of YEAR and DATEPART(week. See fiddle.

Updating destination table based on year-week and date comparison

1 Answers1