MERGE INTO With between production and test where the tables to be merged need a join

I'm writing a stored procedure that should sync a table from our production environment to our test environment (once a day). I have 2 tables. To make it easy lets call them Meters and Measurements.

Meters = [Meter_id, Location]
Measurements = [Meter_id, Value, MeasurementTime]

I want to keep Measurements in sync and lets assume that I can manually add a meter to my test environment. Meter_id can get out of sync between both databases but Location doesn't change.

I have set up a database link to our test server called db_test. Now I would like to use a Merge statement to update Measurements@db_test

MERGE INTO Measurements@db_test meas_test
USING Measurements meas
   ON (???)
WHEN MATCHED THEN
   -- Do update
WHEN NOT MATCHED THEN
   -- Do insert

I was thinking in this direction...

MERGE INTO Measurements@db_test meas_test
USING (SELECT value, location 
       FROM meters mtr, measurements meas 
       WHERE mtr.meter_id = meas.meter_id AND 
       MeasurementTime > sysdate - 1)  meas_new
ON ( meas_new.location = ??? AND 
     meas_new.value = meas_test.value AND 
     meas_new.MeasurementTime = meas_test.MeasurementTime)            
WHEN MATCHED THEN
   -- Do update
WHEN NOT MATCHED THEN
   -- Do insert

So how do I lay the join to the Meters table so that I'm sure they match on location and not on ID

Answers


Can't you just join to the test environment's Meters table over the link? Like this:

MERGE INTO measurements@db_test meas_test
USING (SELECT mtr_test.meter_id, meas.value, meas.measurementtime
         FROM meters mtr, measurements meas, meters@db_test mtr_test
        WHERE mtr.meter_id = meas.meter_id
          AND mtr_test.location = mtr.location
          AND meas.measurementtime > sysdate - 1) meas_new
   ON (meas_new.meter_id = meas_test.meter_id
       AND meas_new.value = meas_test.value
       AND meas_new.measurementtime = meas_test.measurementtime)
 WHEN MATCHED THEN
      -- Do update
 WHEN NOT MATCHED THEN
      -- Do insert

Need Your Help

C++ assert with time stamp

c++ assert

is that possible to log information when assert failed with time stamp

Error while freeing a malloc'd pointer

c pointers memory-management malloc free

I'm getting an error while freeing a malloc'd string in the following piece of code: