gDYRfjjH

1CREATE TABLE IF NOT EXISTS integration.new_reestr_rmsp AS SELECT * FROM dictionary.reestr_rmsp
2WHERE 1=0;
3ALTER TABLE ONLY integration.new_reestr_rmsp ALTER COLUMN insert_date SET DEFAULT '2016-08-26';
4ALTER TABLE ONLY dictionary.reestr_rmsp_arhiv ALTER COLUMN finish_date SET DEFAULT '2016-08-26';
5
6COPY integration.new_reestr_rmsp (poln_naim_ur, sokr_naim_ur, inn_ur, sub_rf, rayon, gorod, nasel_punkt, kod_regiona, kod_vida_okved, naim_vid_deyat_okved, dop_okved, priznak_version_okved, naim_lic_vid_deyat, sved_adr_osush_okved, ser_licence, nomer_licence, vid_licence, data_licenzii, data_nach_lic, data_ok_lic, naim_lic_org_vid, data_paus_lic, naim_lic_org_stopped, kod_vida_prod, naim_vida_prod, priznak_innovac, naim_zak_real_pp, inn_partner, num_dog_partn, data_dogovora_o_pris, naim_zakaz_po_kont, inn_zakaz_kontr, predm_kontr, reestrov_nomer_kontacta, date_zakl_kontracta, naim_zakaz_223, inn_zakaz_223, predmet_dogov_223, reestr_dog_223, data_zak_223, data_vkl_ur_ip, vid_subject_msp, kategor_subject_msp, priznak_nov_sozd_msp) FROM 'E:\OSPanel\domains\monitoring2017\data\files\tmpBulkIntegration\msp5AE4.tmp' WITH DELIMITER ';' NULL '' QUOTE '"' ESCAPE '\' CSV;
7
8INSERT INTO dictionary.reestr_rmsp_arhiv (
9            poln_naim_ur, sokr_naim_ur, inn_ur, sub_rf, rayon, gorod, nasel_punkt, kod_regiona, kod_vida_okved, naim_vid_deyat_okved, dop_okved, priznak_version_okved, naim_lic_vid_deyat, sved_adr_osush_okved, ser_licence, nomer_licence, vid_licence, data_licenzii, data_nach_lic, data_ok_lic, naim_lic_org_vid, data_paus_lic, naim_lic_org_stopped, kod_vida_prod, naim_vida_prod, priznak_innovac, naim_zak_real_pp, inn_partner, num_dog_partn, data_dogovora_o_pris, naim_zakaz_po_kont, inn_zakaz_kontr, predm_kontr, reestrov_nomer_kontacta, date_zakl_kontracta, naim_zakaz_223, inn_zakaz_223, predmet_dogov_223, reestr_dog_223, data_zak_223, data_vkl_ur_ip, vid_subject_msp, kategor_subject_msp, priznak_nov_sozd_msp, start_date
10            ) 
11            SELECT dif.poln_naim_ur, dif.sokr_naim_ur, dif.inn_ur, dif.sub_rf, dif.rayon, dif.gorod, dif.nasel_punkt, dif.kod_regiona, dif.kod_vida_okved, dif.naim_vid_deyat_okved, CAST ( dif.dop_okved AS json), dif.priznak_version_okved, dif.naim_lic_vid_deyat, dif.sved_adr_osush_okved, dif.ser_licence, dif.nomer_licence, dif.vid_licence, dif.data_licenzii, dif.data_nach_lic, dif.data_ok_lic, dif.naim_lic_org_vid, dif.data_paus_lic, dif.naim_lic_org_stopped, dif.kod_vida_prod, dif.naim_vida_prod, dif.priznak_innovac, dif.naim_zak_real_pp, dif.inn_partner, dif.num_dog_partn, dif.data_dogovora_o_pris, dif.naim_zakaz_po_kont, dif.inn_zakaz_kontr, dif.predm_kontr, dif.reestrov_nomer_kontacta, dif.date_zakl_kontracta, dif.naim_zakaz_223, dif.inn_zakaz_223, dif.predmet_dogov_223, dif.reestr_dog_223, dif.data_zak_223, dif.data_vkl_ur_ip, dif.vid_subject_msp, dif.kategor_subject_msp, dif.priznak_nov_sozd_msp,dictionary.reestr_rmsp.insert_date AS start_date FROM (
12            SELECT poln_naim_ur, sokr_naim_ur, inn_ur, sub_rf, rayon, gorod, nasel_punkt, kod_regiona, kod_vida_okved, naim_vid_deyat_okved, CAST (dop_okved AS varchar), priznak_version_okved, naim_lic_vid_deyat, sved_adr_osush_okved, ser_licence, nomer_licence, vid_licence, data_licenzii, data_nach_lic, data_ok_lic, naim_lic_org_vid, data_paus_lic, naim_lic_org_stopped, kod_vida_prod, naim_vida_prod, priznak_innovac, naim_zak_real_pp, inn_partner, num_dog_partn, data_dogovora_o_pris, naim_zakaz_po_kont, inn_zakaz_kontr, predm_kontr, reestrov_nomer_kontacta, date_zakl_kontracta, naim_zakaz_223, inn_zakaz_223, predmet_dogov_223, reestr_dog_223, data_zak_223, data_vkl_ur_ip, vid_subject_msp, kategor_subject_msp, priznak_nov_sozd_msp
13            FROM dictionary.reestr_rmsp
14            EXCEPT
15            SELECT poln_naim_ur, sokr_naim_ur, inn_ur, sub_rf, rayon, gorod, nasel_punkt, kod_regiona, kod_vida_okved, naim_vid_deyat_okved, CAST (dop_okved AS varchar), priznak_version_okved, naim_lic_vid_deyat, sved_adr_osush_okved, ser_licence, nomer_licence, vid_licence, data_licenzii, data_nach_lic, data_ok_lic, naim_lic_org_vid, data_paus_lic, naim_lic_org_stopped, kod_vida_prod, naim_vida_prod, priznak_innovac, naim_zak_real_pp, inn_partner, num_dog_partn, data_dogovora_o_pris, naim_zakaz_po_kont, inn_zakaz_kontr, predm_kontr, reestrov_nomer_kontacta, date_zakl_kontracta, naim_zakaz_223, inn_zakaz_223, predmet_dogov_223, reestr_dog_223, data_zak_223, data_vkl_ur_ip, vid_subject_msp, kategor_subject_msp, priznak_nov_sozd_msp
16            FROM integration.new_reestr_rmsp) AS dif
17            LEFT JOIN dictionary.reestr_rmsp
18            ON dif.inn_ur = dictionary.reestr_rmsp.inn_ur;
19            
20INSERT INTO dictionary.reestr_rmsp 
21                (poln_naim_ur, sokr_naim_ur, inn_ur, sub_rf, rayon, gorod, nasel_punkt, kod_regiona, kod_vida_okved, naim_vid_deyat_okved, dop_okved, priznak_version_okved, naim_lic_vid_deyat, sved_adr_osush_okved, ser_licence, nomer_licence, vid_licence, data_licenzii, data_nach_lic, data_ok_lic, naim_lic_org_vid, data_paus_lic, naim_lic_org_stopped, kod_vida_prod, naim_vida_prod, priznak_innovac, naim_zak_real_pp, inn_partner, num_dog_partn, data_dogovora_o_pris, naim_zakaz_po_kont, inn_zakaz_kontr, predm_kontr, reestrov_nomer_kontacta, date_zakl_kontracta, naim_zakaz_223, inn_zakaz_223, predmet_dogov_223, reestr_dog_223, data_zak_223, data_vkl_ur_ip, vid_subject_msp, kategor_subject_msp, priznak_nov_sozd_msp, insert_date) 
22                SELECT poln_naim_ur, sokr_naim_ur, inn_ur, sub_rf, rayon, gorod, nasel_punkt, kod_regiona, kod_vida_okved, naim_vid_deyat_okved, dop_okved, priznak_version_okved, naim_lic_vid_deyat, sved_adr_osush_okved, ser_licence, nomer_licence, vid_licence, data_licenzii, data_nach_lic, data_ok_lic, naim_lic_org_vid, data_paus_lic, naim_lic_org_stopped, kod_vida_prod, naim_vida_prod, priznak_innovac, naim_zak_real_pp, inn_partner, num_dog_partn, data_dogovora_o_pris, naim_zakaz_po_kont, inn_zakaz_kontr, predm_kontr, reestrov_nomer_kontacta, date_zakl_kontracta, naim_zakaz_223, inn_zakaz_223, predmet_dogov_223, reestr_dog_223, data_zak_223, data_vkl_ur_ip, vid_subject_msp, kategor_subject_msp, priznak_nov_sozd_msp, '2016-08-26' AS insert_date
23                FROM integration.new_reestr_rmsp
24                ON CONFLICT (inn_ur) DO UPDATE 
25                  SET poln_naim_ur = excluded.poln_naim_ur, sokr_naim_ur = excluded.sokr_naim_ur, inn_ur = excluded.inn_ur, sub_rf = excluded.sub_rf, rayon = excluded.rayon, gorod = excluded.gorod, nasel_punkt = excluded.nasel_punkt, kod_regiona = excluded.kod_regiona, kod_vida_okved = excluded.kod_vida_okved, naim_vid_deyat_okved = excluded.naim_vid_deyat_okved, dop_okved = excluded.dop_okved, priznak_version_okved = excluded.priznak_version_okved, naim_lic_vid_deyat = excluded.naim_lic_vid_deyat, sved_adr_osush_okved = excluded.sved_adr_osush_okved, ser_licence = excluded.ser_licence, nomer_licence = excluded.nomer_licence, vid_licence = excluded.vid_licence, data_licenzii = excluded.data_licenzii, data_nach_lic = excluded.data_nach_lic, data_ok_lic = excluded.data_ok_lic, naim_lic_org_vid = excluded.naim_lic_org_vid, data_paus_lic = excluded.data_paus_lic, naim_lic_org_stopped = excluded.naim_lic_org_stopped, kod_vida_prod = excluded.kod_vida_prod, naim_vida_prod = excluded.naim_vida_prod, priznak_innovac = excluded.priznak_innovac, naim_zak_real_pp = excluded.naim_zak_real_pp, inn_partner = excluded.inn_partner, num_dog_partn = excluded.num_dog_partn, data_dogovora_o_pris = excluded.data_dogovora_o_pris, naim_zakaz_po_kont = excluded.naim_zakaz_po_kont, inn_zakaz_kontr = excluded.inn_zakaz_kontr, predm_kontr = excluded.predm_kontr, reestrov_nomer_kontacta = excluded.reestrov_nomer_kontacta, date_zakl_kontracta = excluded.date_zakl_kontracta, naim_zakaz_223 = excluded.naim_zakaz_223, inn_zakaz_223 = excluded.inn_zakaz_223, predmet_dogov_223 = excluded.predmet_dogov_223, reestr_dog_223 = excluded.reestr_dog_223, data_zak_223 = excluded.data_zak_223, data_vkl_ur_ip = excluded.data_vkl_ur_ip, vid_subject_msp = excluded.vid_subject_msp, kategor_subject_msp = excluded.kategor_subject_msp, priznak_nov_sozd_msp = excluded.priznak_nov_sozd_msp
26                  ,insert_date = '2016-08-26';
27
28
29CREATE TEMP TABLE tmp AS
30            SELECT d.*
31            FROM   integration.new_reestr_rmsp t
32            LEFT JOIN dictionary.reestr_rmsp d USING (inn_ur);
33TRUNCATE dictionary.reestr_rmsp;
34INSERT INTO dictionary.reestr_rmsp
35SELECT * FROM tmp;
36
37DROP TABLE tmp;
38TRUNCATE TABLE integration.new_reestr_rmsp;
39DROP TABLE integration.new_reestr_rmsp;