cc_migr

create table cc_stage(
	cc_num int,
	cc_mgr varchar
);

create table cc_prod(
	cc_num int
	cc_mgr varchar,
	valid_from date,
	valid_to date,
	current_flag boolean
);

insert into cc_stage VALUES (1, 'dbarrat');

delete from cc_prod;

INSERT INTO cc_prod(cc_num, cc_mgr, valid_from, valid_to, current_flag)
 (
 	SELECT cc_num, cc_mgr, CURRENT_DATE, null, true
	FROM cc_stage cs 
	WHERE NOT EXISTS (
		SELECT * 
	    FROM cc_prod cp
	    WHERE cs.cc_num  = cp.cc_num
	)
 );

UPDATE cc_prod
SET current_flag = false, valid_to = current_date
where exists (
  SELECT *
  FROM cc_stage cs
  WHERE cs.cc_num = cc_prod.cc_num 
	  and cs.cc_mgr <> cc_prod.cc_mgr
	  and current_flag  = true
 );

insert into cc_prod(cc_num, cc_mgr, valid_from, valid_to, current_flag)
(
	SELECT cc_num, cc_mgr, CURRENT_DATE, null, true
	FROM cc_stage cs 
	WHERE EXISTS (
		SELECT * 
	    FROM cc_prod cp
	    WHERE cs.cc_num  = cp.cc_num
	    and cs.cc_mgr <> cp.cc_mgr
	    and cp.valid_to = CURRENT_DATE 
	)
);