CODEWITHSUNDEEP
sqlsql-servert-sql
Zerjack
Zerjack

Reputation: 131

Merge 2 tables and pull the latest record from them

Say I have two customer tables which have pretty much the same columns. One of them is a temporal one which periodically updates the other one. That is, updates and additions to the records are done to the temporal table only. Say they're names are CUSTOMER, and CUSTOMER_TEMP.

The tables got information like ID, NAME, LAST_NAME, and ADDRESS.

This temporal table has three extra fields, TEMP_ID, RECORD_TYPE, and DATE. The record type is used to record whether there was an addition or an update. So the thing is I need to select the latest record from both tables. That involves several cases

Now, I don't know whether this is a good flow or not. I was just told to make the query, so I don't have access to the DB, I believe I could make suggestions though. The thing is My SLQ knowledge is not enough to build this query. I know there's an INNER_JOIN involved, as well as a filter by date, and probably and EXIST, to check whether the record exist or not in the CUSTOMER_TEMP table. But I don't quite know how to build it. I'm working on .Net And SQLServer. Any help on it is quite appreciated.

Upvotes: 0

Views: 361

Answers (2)

paparazzo
paparazzo

Reputation: 45096

select m.*, 0 as [rn]
from main m 
where not exists (select 1 from temp where temp.id = m.id) 
union 
select tt.* 
from ( select temp.*
            , row_number() over (partition by id order by RECORD_TYPE desc, date desc) as rn
         from temp 
      -- join main 
      --       on temp.ID = main.ID 
     ) tt
where tt.rn = 1

if update does not sort last then need to do a trick like in the answer from Tom H

Upvotes: 2

Tom H
Tom H

Reputation: 47464

;WITH CTE_Latest_Temporal AS
(
    SELECT
        id,
        name,
        ...,    -- Put the rest of your columns here
        ROW_NUMBER OVER (PARTITION BY id
                        ORDER BY
                            CASE record_type
                                WHEN 'Update' THEN 0
                                ELSE 1
                            END, date DESC) AS row_num
    FROM
        Customer_Temp
)
SELECT
    M.id,
    CASE WHEN T.id IS NOT NULL THEN T.name ELSE M.name END AS name,
    ...    -- Similar CASE statements for the rest of your columns
FROM
    Customer M
LEFT OUTER JOIN CTE_Latest_Temporal T ON
    T.id = M.id AND
    T.row_num = 1

The CASE statements can be replaced by a simple COALESCE(T.column, M.column) for any columns that cannot be NULL. I had to use the CASE to cover situations where the row might exist in the temp table but the column might be NULL in the temp table, but have a value in the main table.

Upvotes: 1

Related Questions