Merge Tables in Oracle

1_merge.sql

DROP TABLE data_staging;
DROP TABLE data;

CREATE TABLE data_staging 
(
  id number(10) not null,
  name nvarchar2(50) not null,
  city nvarchar2(20)
);

CREATE TABLE data
(
  id number(10) not null,
  name nvarchar2(50) not null,
  city nvarchar2(20),
  constraint data_staging_pk PRIMARY KEY (id)
);

2_insert.sql

-- insert new rows

TRUNCATE TABLE data_staging;
INSERT INTO data_staging (id, name, city) VALUES (1, 'John', 'Melbourne');
INSERT INTO data_staging (id, name, city) VALUES (2, 'Jacob', 'Sydney');
INSERT INTO data_staging (id, name, city) VALUES (3, 'James', 'Perth');
INSERT INTO data_staging (id, name, city) VALUES (4, 'Jerry', 'Adelaide');
INSERT INTO data_staging (id, name, city) VALUES (5, 'Jason', 'Brisbane');

TRUNCATE TABLE data;
INSERT INTO data (id, name, city) VALUES (1, 'John', 'Melbourne');
INSERT INTO data (id, name, city) VALUES (2, 'Jacob', 'Sydney');
INSERT INTO data (id, name, city) VALUES (3, 'James', 'Perth');

SELECT * FROM data;

MERGE INTO data 
  USING data_staging
  ON (data.id = data_staging.id)
  WHEN NOT MATCHED THEN INSERT (id, name, city) VALUES (data_staging.id, data_staging.name, data_staging.city);

SELECT * FROM data;

3_modify.sql

-- modify existing rows

TRUNCATE TABLE data_staging;
INSERT INTO data_staging (id, name, city) VALUES (1, 'John', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (2, 'Jacob', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (3, 'James', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (4, 'Jerry', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (5, 'Jason', 'Cairns');

TRUNCATE TABLE data;
INSERT INTO data (id, name, city) VALUES (1, 'John', 'Melbourne');
INSERT INTO data (id, name, city) VALUES (2, 'Jacob', 'Sydney');
INSERT INTO data (id, name, city) VALUES (3, 'James', 'Perth');
INSERT INTO data (id, name, city) VALUES (4, 'Jerry', 'Adelaide');
INSERT INTO data (id, name, city) VALUES (5, 'Jason', 'Brisbane');

SELECT * FROM data;

MERGE INTO data 
  USING data_staging
  ON (data.id = data_staging.id)
  WHEN MATCHED THEN UPDATE SET name=data_staging.name, city=data_staging.city
  WHEN NOT MATCHED THEN INSERT (id, name, city) VALUES (data_staging.id, data_staging.name, data_staging.city);

SELECT * FROM data;

4_sync.sql

TRUNCATE TABLE data_staging;
INSERT INTO data_staging (id, name, city) VALUES (1, 'John', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (2, 'Jacob', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (3, 'James', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (5, 'Jason', 'Cairns');

TRUNCATE TABLE data;
INSERT INTO data (id, name, city) VALUES (1, 'John', 'Melbourne');
INSERT INTO data (id, name, city) VALUES (2, 'Jacob', 'Sydney');
INSERT INTO data (id, name, city) VALUES (3, 'James', 'Perth');
INSERT INTO data (id, name, city) VALUES (4, 'Jerry', 'Adelaide');
INSERT INTO data (id, name, city) VALUES (5, 'Jason', 'Brisbane');

SELECT * FROM data;

DELETE FROM data WHERE id NOT IN (SELECT ID from data_staging);

MERGE INTO data 
  USING data_staging
  ON (data.id = data_staging.id)
  WHEN MATCHED THEN UPDATE SET name=data_staging.name, city=data_staging.city
  WHEN NOT MATCHED THEN INSERT (id, name, city) VALUES (data_staging.id, data_staging.name, data_staging.city);

SELECT * FROM data;

5_multi_table.sql

DROP TABLE cities;
CREATE TABLE cities
(
  name nvarchar2(20) not null,
  population number(19),
  constraint cities_pk PRIMARY KEY (name)
)

DROP TABLE data;
CREATE TABLE data
(
  id number(10) not null,
  name nvarchar2(50) not null,
  city nvarchar2(20),
  constraint data_pk PRIMARY KEY (id),
  constraint data_city_fk FOREIGN KEY(city) REFERENCES cities (name)
);

TRUNCATE TABLE cities;
INSERT INTO cities (name, population) VALUES ('Melbourne', 4000000);
INSERT INTO cities (name, population) VALUES ('Sydney', 4100000);
INSERT INTO cities (name, population) VALUES ('Brisbane', 3000000);
INSERT INTO cities (name, population) VALUES ('Adelaide', 2900000);
INSERT INTO cities (name, population) VALUES ('Perth', null);

TRUNCATE TABLE data_staging;
INSERT INTO data_staging (id, name, city) VALUES (1, 'John', 'Cairns');
INSERT INTO data_staging (id, name, city) VALUES (2, 'Jacob', 'Melbourne');
INSERT INTO data_staging (id, name, city) VALUES (3, 'James', 'Adelaide');
INSERT INTO data_staging (id, name, city) VALUES (5, 'Jason', 'Cairns');

TRUNCATE TABLE data;
INSERT INTO data (id, name, city) VALUES (1, 'John', 'Melbourne');
INSERT INTO data (id, name, city) VALUES (2, 'Jacob', 'Sydney');
INSERT INTO data (id, name, city) VALUES (3, 'James', 'Perth');
INSERT INTO data (id, name, city) VALUES (4, 'Jerry', 'Adelaide');
INSERT INTO data (id, name, city) VALUES (5, 'Jason', 'Brisbane');

SELECT * FROM cities;

MERGE INTO cities 
  USING (SELECT DISTINCT city FROM data_staging WHERE city <> null)
  ON (cities.name = city)
  WHEN MATCHED THEN UPDATE SET population=cities.population
  WHEN NOT MATCHED THEN INSERT (name, population) VALUES (city, null);
  
SELECT * FROM cities;

SELECT * FROM data;

DELETE FROM data WHERE id NOT IN (SELECT ID from data_staging);

MERGE INTO data 
  USING data_staging
  ON (data.id = data_staging.id)
  WHEN MATCHED THEN UPDATE SET name=data_staging.name, city=data_staging.city -- yes this is a hack!
  WHEN NOT MATCHED THEN INSERT (id, name, city) VALUES (data_staging.id, data_staging.name, data_staging.city);

SELECT * FROM data;