1 -- Import complet des adresses
3 DROP TABLE IF EXISTS fusionax_adresses
;
5 CREATE TABLE IF NOT EXISTS fusionax_adresses (
6 provenance
CHAR(2) NOT NULL COMMENT 'Vaut AC, AD ou AN selon la provenance de l''info',
7 id_ancien
VARCHAR(8) NOT NULL COMMENT 'Id unique de l''ancien',
8 Type_adr
ENUM('E', 'P') character set binary NOT NULL DEFAULT 'P' COMMENT 'Type d''adresse : E pour Entreprise, P pour Personnelle',
9 hash_adresse
BINARY(5) NOT NULL COMMENT 'Hash pour différencier les diverses adresses',
10 Ligne1
VARCHAR(90) NOT NULL,
11 Ligne2
VARCHAR(90) NOT NULL,
12 Ligne3
VARCHAR(90) NOT NULL,
13 code_postal
VARCHAR(20) NOT NULL,
14 ville
VARCHAR(80) NOT NULL,
15 zip_cedex
VARCHAR(20) NOT NULL,
16 etat_distr
VARCHAR(20) NOT NULL,
17 pays
VARCHAR(50) NOT NULL,
18 tel
VARCHAR(30) NOT NULL,
19 fax
VARCHAR(30) NOT NULL,
20 Date_maj
DATE NOT NULL,
21 PRIMARY KEY (id_ancien
, hash_adresse
)
22 ) ENGINE
=InnoDB
, CHARSET
=utf8
;
24 LOAD DATA LOCAL INFILE
'{?}Adresses.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED
BY '\t' LINES TERMINATED
BY '\r\n'
25 (provenance
, id_ancien
, @Type_adr
, Ligne1
, Ligne2
, Ligne3
, code_postal
, ville
, zip_cedex
, etat_distr
, pays
, tel
, fax
, @StringDate_maj
)
27 `Type_adr`
= IF(@Type_adr
= 'E', 'E', IF(@Type_adr
= '', '', 'P')),
28 `hash_adresse`
= SUBSTRING( MD5( @Type_adr
), 1, 5),
29 `Date_maj`
= CONCAT(SUBSTRING(@StringDate_maj
,7),'-',SUBSTRING(@StringDate_maj
,4,2),'-',SUBSTRING(@StringDate_maj
,1,2));
31 LOAD DATA LOCAL INFILE
'{?}Anciens.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED
BY '\t' LINES TERMINATED
BY '\r\n'
32 (provenance
, id_ancien
, @
Login, @
Password, @promotion_etude
, @Groupe_promo
, @Nom_patronymique
, @partic_patro
, @prenom
, @Nom_usuel
, @partic_nom
,
33 @Nom_complet
, @Civilite
, @Code_nationalite
, @Type_membre
, @corps_sortie
, @StringDate_deces
, @grade
, @Mel_usage
, @Mel_publiable
,
34 @tel_mobile
, @annee_dernCot
, @Representant
, @Type_adr
, Ligne1
, Ligne2
, Ligne3
, code_postal
, ville
,
35 zip_cedex
, etat_distr
, pays
, tel
, fax
, @StringDate_maj
)
37 `Type_adr`
= IF(@Type_adr
= 'E', 'E', IF(@Type_adr
= '', '', 'P')),
38 `hash_adresse`
= SUBSTRING( MD5( @Type_adr
), 1, 5),
39 `Date_maj`
= CONCAT(SUBSTRING(@StringDate_maj
,7),'-',SUBSTRING(@StringDate_maj
,4,2),'-',SUBSTRING(@StringDate_maj
,1,2));
41 LOAD DATA LOCAL INFILE
'{?}Activites.txt' INTO TABLE `fusionax_adresses` FIELDS TERMINATED
BY '\t' LINES TERMINATED
BY '\r\n'
42 (provenance
, id_ancien
, @Code_etab
, @Raison_sociale
, @Libelle_fonctio
, @Annuaire
,
43 Ligne1
, Ligne2
, Ligne3
, code_postal
, ville
, zip_cedex
, etat_distr
, pays
, tel
, fax
, @StringDate_maj
)
46 `hash_adresse`
= SUBSTRING( MD5( CONCAT(@Code_etab
, @Libelle_fonctio
) ), 1, 5),
47 `Date_maj`
= CONCAT(SUBSTRING(@StringDate_maj
,7),'-',SUBSTRING(@StringDate_maj
,4,2),'-',SUBSTRING(@StringDate_maj
,1,2));