How To Insert, Update, Delete When Import Data From Table To Table?

I have a query that I need to run more than once a day. This query is importing data from a database to another. The target table structure is: Id Date Department Loca

Solution 1:

you may use a merge statement.. something like this...

with target_T as (select * from UR_TARGET_TABLE
                  where statesource is not null) -- to dont change the data inserted from application...
merge target_T as TARGET
using UR_SOURCE_TABLE as SOURCE
on SOURCE.id = TARGET.id    -- id is unique? anyway, put your primary key here...

when matched and TARGET.state = TARGET.statesource then --if inserted/updated from application, will not change data
update set TARGET.state = SOURCE.state
          ,TARGET.statesource = SOURCE.state  --important update it together to be different from an application update
        --, other collumns that you have to set...

--should use another when matched then update if need to change something on inserted/updated from application data


when not matched by TARGET then
         insert (Id, Date, Department, Location, PersonId, Starttime, EndTime,State, StateSource)
         values(SOURCE.Id, SOURCE.Date, SOURCE.Department, SOURCE.Location, SOURCE.PersonId, SOURCE.Starttime, SOURCE.EndTime,SOURCE.State, SOURCE.StateSource);

if you set an sample with declaring your tables and inserting some data...

I should help more, with a code that really works.. not just a sample...