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
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