Updating rows with same date and same id while loading next file(s) - could it be Merge statement? (i used it once in the past i think).

Question

Updating rows with same date and same id while loading next file(s) - could it be Merge statement? (i used it once in the past i think).

VoldemarG

Hall of Fame

Points: 3883
More actions
December 28, 2018 at 3:16 pm

#409402

i have 2 FILES (.CSV).
I am just dump loading the raw contents of each into Table1 and Table2 staging table..
THen i need to insert ONLY NEW DATA into the TABLE_TARGET
(same structure as staging tables Table1 and Table2).
I need to insert new row if a row or rows with same id and dtOccurrenceDate field value does not exist,
and update the existing rows the TABLE_TARGET where ID and dtOccurrenceDate field value match., from both table1 and table2
How do i even start ?.... (the holidays also caused partial amnesia to me. I could swear I knew how to do it and did it in the past 🙁 )
Please, give me a hint. Also, one server where such sql should run is a 2000.. so no merge statement there 🙁
Likes to play Chess

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

VoldemarG - Friday, December 28, 2018 3:16 PM
i have 2 FILES (.CSV).
I am just dump loading the raw contents of each into Table1 and Table2 staging table..
THen i need to insert ONLY NEW DATA into the TABLE_TARGET
(same structure as staging tables Table1 and Table2).
I need to insert new row if a row or rows with same id and dtOccurrenceDate field value does not exist,
and update the existing rows the TABLE_TARGET where ID and dtOccurrenceDate field value match., from both table1 and table2
How do i even start ?.... (the holidays also caused partial amnesia to me. I could swear I knew how to do it and did it in the past 🙁 )
Please, give me a hint. Also, one server where such sql should run is a 2000.. so no merge statement there 🙁

You could use EXISTS or IN for the comparison
😎

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

I ended up doing it this way, because same scrtipt needs to be run on both 2016 and 2000 SQL Servers:

--create first table
CREATE TABLE #tblTarget (ID INT, DateOfOccurrence DATE)
INSERT INTO #tblTarget VALUES(1, '01/01/2018'), (2, '01/02/2018')
--create second table with with same rows on fly
CREATE TABLE #tblStage (ID INT, DateOfOccurrence DATE)
INSERT INTO #tblStage VALUES(2, '01/04/2018'), (3, '01/05/2018'), (4, '01/06/2018')
;
SELECT * FROM #tblTarget
SELECT * FROM #tblStage
;
BEGIN TRAN
    -- update already existing rows first
    UPDATE T1
    SET T1.DateOfOccurrence = T2.DateOfOccurrence
    FROM #tblStage T2
    JOIN #tblTarget T1
        ON T1.ID = T2.ID
        and T1.DateOfOccurrence= T2.DateOfOccurrence
    WHERE T1.ID IS NOT NULL

    -- insert new rows
    INSERT INTO #tblTarget (ID, DateOfOccurrence)
    SELECT T2.ID, T2.DateOfOccurrence
    FROM #tblStage T2
    LEFT JOIN #tblTarget T1
        ON T1.ID = T2.ID
         and T1.DateOfOccurrence= T2.DateOfOccurrence
    WHERE T1.ID IS NULL
COMMIT TRAN
;
SELECT * FROM #tblTarget
;
DROP TABLE #tblStage
DROP TABLE #tblTarget

Likes to play Chess

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 3

The UPDATE will set the DateOfOccurrence to its existing value (ie. have no effect) because the column is also part of the JOIN criteria.