CODEWITHSUNDEEP
sqlsql-updatesql-insert
user2715877
user2715877

Reputation: 543

SQL - UPDATE/INSERT FROM Source to Target table

I have a source table called STG which needs to be "merged" with a target table called MAP.

STG
ID1    HASH    TIME
A     123     12:01AM
A     456     12:05AM
A     789     12:10AM
B     123     12:15AM
B     789     12:20AM
C     987     12:25AM

MAP
ID1   ID2     HASH    TIME
A     A       123     11:55PM

ID1 in MAP can have the same column values as in STG The value for ID2 is determined by the first time MAP sees the ID1 and HASH combination. For example, if row 4, B 123 12:15AM, were to be inserted as a single row into MAP it woudd look like B A 123 12:15AM due to the fact that the HASH value 123 was already in MAP, so the existing value of A ID2 is used.

Goal: "Merge" all the rows in STG into MAP with the appropriate ID2 value.

Cases:

  1. ID1 Exists in MAP, HASH Exists in MAP: UPDATE(MAP.TIME = STG.TIME)

  2. ID1 DNE in MAP, HASH DNE in MAP: INSERT(STG.ID1, STG.ID1, STG.HASH, STG.TIME)

  3. ID1 Exists in MAP, HASH DNE in MAP: INSERT(STG.ID1, MAP.ID2, STG.HASH, STG.TIME)
  4. ID1 DNE in MAP, HASH Exists in MAP: INSERT(STG.ID1, MAP.ID2, STG.HASH, STG.TIME)

The problem with tackling the problem in this manner is that some of the rows in STG have dependencies on one another. For instance, I the SQL code to execute Case 2 is ran, the 5th row will be inserted as neither ID1 and HASH exist in MAP, but the 4th row has an association with MAP via HASH value 123.

Cases 1 and 2, are straight forward and obvious. Solving for the Cases 3 and 4 while considering the dependencies has proved difficult.

How should Cases 3 and 4 be solved?

The (correct) end result should look like this...

MAP
ID1    ID2    HASH    TIME
A      A      123     12:01AM
A      A      456     12:05AM
A      A      789     12:10AM
B      A      123     12:15AM
B      A      789     12:20AM
C      C      987     12:25AM

Upvotes: 0

Views: 358

Answers (1)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this.

DECLARE @STG TABLE (ID1 VARCHAR(5),   [HASH]  INT,  [TIME] TIME)
INSERT INTO @STG VALUES
('A',123,'12:01AM'),
('A',456,'12:05AM'),
('A',789,'12:10AM'),
('B',123,'12:15AM'),
('B',789,'12:20AM'),
('C',987,'12:25AM')

DECLARE @MAP TABLE (ID1 VARCHAR(5), ID2 VARCHAR(5),   [HASH]  INT,  [TIME] TIME)
INSERT INTO @MAP 
VALUES
('A','A',123,'11:55PM')


;MERGE @MAP AS tar
USING (
        SELECT S.ID1, COALESCE(M.ID1, S.ID1) ID2 , S.HASH, S.TIME 
        FROM @STG S
            LEFT JOIN @MAP M ON (S.HASH = M.HASH OR S.ID1 = M.ID1)
        ) AS src (ID1, ID2, [HASH], [TIME])
ON (tar.ID1 = src.ID1 AND tar.[HASH] = src.[HASH])
WHEN MATCHED THEN
    UPDATE SET [TIME] = src.[TIME]
WHEN NOT MATCHED THEN
    INSERT (ID1, ID2, [HASH], [TIME])
    VALUES (src.ID1, src.ID2, src.[HASH], src.[TIME]) ;

Result:

ID1   ID2   HASH        TIME
----- ----- ----------- ----------------
A     A     123         00:01:00.0000000
A     A     456         00:05:00.0000000
A     A     789         00:10:00.0000000
B     A     123         00:15:00.0000000
B     B     789         00:20:00.0000000
C     C     987         00:25:00.0000000

Upvotes: 1

Related Questions