Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test_table
(
code varchar2(10) not null,
type varchar2(50) not null,
start_date date not null,
end_date date not null,
parameter number
);
insert into test_table (code, type, start_date, end_date, parameter)
values ('CODE1', 'a', DATE '2021-01-01', DATE '2021-01-10', 1);
1 rows affected

MERGE INTO test_table dst
USING (
  WITH new_data (code, type, start_date, end_date, parameter) AS (
    SELECT 'CODE2', 'a', DATE '2021-01-01', DATE '2021-01-20', 2 FROM DUAL
  )
  SELECT NULL AS rid,
         n.*,
         0 AS status -- Insert
  FROM   new_data n
  UNION ALL
  -- Existing rows overlapping before
  SELECT t.ROWID,
         t.code,
         t.type,
         t.start_date,
         n.start_date - INTERVAL '1' DAY,
         t.parameter,
         1 -- Update overlap before
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.start_date
            AND t.end_date   >= n.start_date)
  UNION ALL
  SELECT t.ROWID,
         t.code,
         t.type,
         n.end_date + INTERVAL '1' DAY,
         t.end_date,
         t.parameter,
         CASE
         WHEN n.start_date <= t.end_date   AND t.end_date <= n.end_date
         THEN 2 -- Delete
         WHEN t.start_date < n.start_date  AND n.end_date <  t.end_date
         THEN 0 -- Insert overlap afterwards
         ELSE 1 -- Update overlap afterwards
         END
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.end_date
            AND t.end_date   >= n.start_date)
  WHERE  NOT (t.start_date <= n.start_date AND t.end_date <= n.end_date)
) src
ON (src.rid = dst.ROWID AND status > 0)
WHEN MATCHED THEN
  UPDATE
  SET    code       = src.code,
         start_date = src.start_date,
         end_date   = src.end_date
  DELETE
  WHERE status = 2
  OR    src.start_date > src.end_date
WHEN NOT MATCHED THEN
  INSERT (code, type, start_date, end_date, parameter)
  VALUES (src.code, src.type, src.start_date, src.end_date, src.parameter);
2 rows affected

SELECT * FROM test_table ORDER BY start_date;


CODE
TYPE
START_DATE
END_DATE
PARAMETER


CODE2
a
01-JAN-21
20-JAN-21
2


MERGE INTO test_table dst
USING (
  WITH new_data (code, type, start_date, end_date, parameter) AS (
    SELECT 'CODE3', 'a', DATE '2021-01-15', DATE '2021-01-16', 3 FROM DUAL
  )
  SELECT NULL AS rid,
         n.*,
         0 AS status -- Insert
  FROM   new_data n
  UNION ALL
  -- Existing rows overlapping before
  SELECT t.ROWID,
         t.code,
         t.type,
         t.start_date,
         n.start_date - INTERVAL '1' DAY,
         t.parameter,
         1 -- Update overlap before
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.start_date
            AND t.end_date   >= n.start_date)
  UNION ALL
  SELECT t.ROWID,
         t.code,
         t.type,
         n.end_date + INTERVAL '1' DAY,
         t.end_date,
         t.parameter,
         CASE
         WHEN n.start_date <= t.end_date   AND t.end_date <= n.end_date
         THEN 2 -- Delete
         WHEN t.start_date < n.start_date  AND n.end_date <  t.end_date
         THEN 0 -- Insert overlap afterwards
         ELSE 1 -- Update overlap afterwards
         END
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.end_date
            AND t.end_date   >= n.start_date)
  WHERE  NOT (t.start_date <= n.start_date AND t.end_date <= n.end_date)
) src
ON (src.rid = dst.ROWID AND status > 0)
WHEN MATCHED THEN
  UPDATE
  SET    code       = src.code,
         start_date = src.start_date,
         end_date   = src.end_date
  DELETE
  WHERE status = 2
  OR    src.start_date > src.end_date
WHEN NOT MATCHED THEN
  INSERT (code, type, start_date, end_date, parameter)
  VALUES (src.code, src.type, src.start_date, src.end_date, src.parameter);
3 rows affected

SELECT * FROM test_table ORDER BY start_date;


CODE
TYPE
START_DATE
END_DATE
PARAMETER


CODE2
a
01-JAN-21
14-JAN-21
2

CODE3
a
15-JAN-21
16-JAN-21
3

CODE2
a
17-JAN-21
20-JAN-21
2


MERGE INTO test_table dst
USING (
  WITH new_data (code, type, start_date, end_date, parameter) AS (
    SELECT 'CODE4', 'a', DATE '2021-01-19', DATE '2021-01-26', 4 FROM DUAL
  )
  SELECT NULL AS rid,
         n.*,
         0 AS status -- Insert
  FROM   new_data n
  UNION ALL
  -- Existing rows overlapping before
  SELECT t.ROWID,
         t.code,
         t.type,
         t.start_date,
         n.start_date - INTERVAL '1' DAY,
         t.parameter,
         1 -- Update overlap before
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.start_date
            AND t.end_date   >= n.start_date)
  UNION ALL
  SELECT t.ROWID,
         t.code,
         t.type,
         n.end_date + INTERVAL '1' DAY,
         t.end_date,
         t.parameter,
         CASE
         WHEN n.start_date <= t.end_date   AND t.end_date <= n.end_date
         THEN 2 -- Delete
         WHEN t.start_date < n.start_date  AND n.end_date <  t.end_date
         THEN 0 -- Insert overlap afterwards
         ELSE 1 -- Update overlap afterwards
         END
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.end_date
            AND t.end_date   >= n.start_date)
  WHERE  NOT (t.start_date <= n.start_date AND t.end_date <= n.end_date)
) src
ON (src.rid = dst.ROWID AND status > 0)
WHEN MATCHED THEN
  UPDATE
  SET    code       = src.code,
         start_date = src.start_date,
         end_date   = src.end_date
  DELETE
  WHERE status = 2
  OR    src.start_date > src.end_date
WHEN NOT MATCHED THEN
  INSERT (code, type, start_date, end_date, parameter)
  VALUES (src.code, src.type, src.start_date, src.end_date, src.parameter);
2 rows affected

SELECT * FROM test_table ORDER BY start_date;


CODE
TYPE
START_DATE
END_DATE
PARAMETER


CODE2
a
01-JAN-21
14-JAN-21
2

CODE3
a
15-JAN-21
16-JAN-21
3

CODE2
a
17-JAN-21
18-JAN-21
2

CODE4
a
19-JAN-21
26-JAN-21
4


MERGE INTO test_table dst
USING (
  WITH new_data (code, type, start_date, end_date, parameter) AS (
    SELECT 'CODE5', 'a', DATE '2020-12-30', DATE '2021-02-01', 5 FROM DUAL
  )
  SELECT NULL AS rid,
         n.*,
         0 AS status -- Insert
  FROM   new_data n
  UNION ALL
  -- Existing rows overlapping before
  SELECT t.ROWID,
         t.code,
         t.type,
         t.start_date,
         n.start_date - INTERVAL '1' DAY,
         t.parameter,
         1 -- Update overlap before
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.start_date
            AND t.end_date   >= n.start_date)
  UNION ALL
  SELECT t.ROWID,
         t.code,
         t.type,
         n.end_date + INTERVAL '1' DAY,
         t.end_date,
         t.parameter,
         CASE
         WHEN n.start_date <= t.end_date   AND t.end_date <= n.end_date
         THEN 2 -- Delete
         WHEN t.start_date < n.start_date  AND n.end_date <  t.end_date
         THEN 0 -- Insert overlap afterwards
         ELSE 1 -- Update overlap afterwards
         END
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.end_date
            AND t.end_date   >= n.start_date)
  WHERE  NOT (t.start_date <= n.start_date AND t.end_date <= n.end_date)
) src
ON (src.rid = dst.ROWID AND status > 0)
WHEN MATCHED THEN
  UPDATE
  SET    code       = src.code,
         start_date = src.start_date,
         end_date   = src.end_date
  DELETE
  WHERE status = 2
  OR    src.start_date > src.end_date
WHEN NOT MATCHED THEN
  INSERT (code, type, start_date, end_date, parameter)
  VALUES (src.code, src.type, src.start_date, src.end_date, src.parameter);
5 rows affected

SELECT * FROM test_table ORDER BY start_date;


CODE
TYPE
START_DATE
END_DATE
PARAMETER


CODE5
a
30-DEC-20
01-FEB-21
5


MERGE INTO test_table dst
USING (
  WITH new_data (code, type, start_date, end_date, parameter) AS (
    SELECT 'CODE6', 'a', DATE '2020-12-20', DATE '2020-12-25', 6 FROM DUAL
  )
  SELECT NULL AS rid,
         n.*,
         0 AS status -- Insert
  FROM   new_data n
  UNION ALL
  -- Existing rows overlapping before
  SELECT t.ROWID,
         t.code,
         t.type,
         t.start_date,
         n.start_date - INTERVAL '1' DAY,
         t.parameter,
         1 -- Update overlap before
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.start_date
            AND t.end_date   >= n.start_date)
  UNION ALL
  SELECT t.ROWID,
         t.code,
         t.type,
         n.end_date + INTERVAL '1' DAY,
         t.end_date,
         t.parameter,
         CASE
         WHEN n.start_date <= t.end_date   AND t.end_date <= n.end_date
         THEN 2 -- Delete
         WHEN t.start_date < n.start_date  AND n.end_date <  t.end_date
         THEN 0 -- Insert overlap afterwards
         ELSE 1 -- Update overlap afterwards
         END
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.end_date
            AND t.end_date   >= n.start_date)
  WHERE  NOT (t.start_date <= n.start_date AND t.end_date <= n.end_date)
) src
ON (src.rid = dst.ROWID AND status > 0)
WHEN MATCHED THEN
  UPDATE
  SET    code       = src.code,
         start_date = src.start_date,
         end_date   = src.end_date
  DELETE
  WHERE status = 2
  OR    src.start_date > src.end_date
WHEN NOT MATCHED THEN
  INSERT (code, type, start_date, end_date, parameter)
  VALUES (src.code, src.type, src.start_date, src.end_date, src.parameter);
1 rows affected

SELECT * FROM test_table ORDER BY start_date;


CODE
TYPE
START_DATE
END_DATE
PARAMETER


CODE6
a
20-DEC-20
25-DEC-20
6

CODE5
a
30-DEC-20
01-FEB-21
5


MERGE INTO test_table dst
USING (
  WITH new_data (code, type, start_date, end_date, parameter) AS (
    SELECT 'CODE7', 'a', DATE '2020-12-19', DATE '2020-12-30', 7 FROM DUAL
  )
  SELECT NULL AS rid,
         n.*,
         0 AS status -- Insert
  FROM   new_data n
  UNION ALL
  -- Existing rows overlapping before
  SELECT t.ROWID,
         t.code,
         t.type,
         t.start_date,
         n.start_date - INTERVAL '1' DAY,
         t.parameter,
         1 -- Update overlap before
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.start_date
            AND t.end_date   >= n.start_date)
  UNION ALL
  SELECT t.ROWID,
         t.code,
         t.type,
         n.end_date + INTERVAL '1' DAY,
         t.end_date,
         t.parameter,
         CASE
         WHEN n.start_date <= t.end_date   AND t.end_date <= n.end_date
         THEN 2 -- Delete
         WHEN t.start_date < n.start_date  AND n.end_date <  t.end_date
         THEN 0 -- Insert overlap afterwards
         ELSE 1 -- Update overlap afterwards
         END
  FROM   test_table t
         INNER JOIN new_data n
         ON (   t.start_date <= n.end_date
            AND t.end_date   >= n.start_date)
  WHERE  NOT (t.start_date <= n.start_date AND t.end_date <= n.end_date)
) src
ON (src.rid = dst.ROWID AND status > 0)
WHEN MATCHED THEN
  UPDATE
  SET    code       = src.code,
         start_date = src.start_date,
         end_date   = src.end_date
  DELETE
  WHERE status = 2
  OR    src.start_date > src.end_date
WHEN NOT MATCHED THEN
  INSERT (code, type, start_date, end_date, parameter)
  VALUES (src.code, src.type, src.start_date, src.end_date, src.parameter);
3 rows affected

SELECT * FROM test_table ORDER BY start_date;


CODE
TYPE
START_DATE
END_DATE
PARAMETER


CODE7
a
19-DEC-20
30-DEC-20
7

CODE5
a
31-DEC-20
01-FEB-21
5
CODE	TYPE	START_DATE	END_DATE	PARAMETER
CODE2	a	01-JAN-21	14-JAN-21	2
CODE3	a	15-JAN-21	16-JAN-21	3
CODE2	a	17-JAN-21	20-JAN-21	2