101 lines
2.9 KiB
SQL
101 lines
2.9 KiB
SQL
# macro_tables.mysql
|
|
-- Create the temporary macro table containing all the data.
|
|
-- This table will be used to make the relations between the tables,
|
|
-- and will then be deleted.
|
|
-- Usage: $ mysql -u efrei -p -D bdd_project < macro_tables.mysql
|
|
# ==================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS MACRO_DRAFT (
|
|
id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Macro_pk PRIMARY KEY (id),
|
|
Date_mutation DATE NOT NULL,
|
|
Nature_mutation VARCHAR(50) NOT NULL,
|
|
Valeur_fonciere DOUBLE PRECISION (20, 2) NOT NULL,
|
|
No_voie VARCHAR(10),
|
|
B_T_Q VARCHAR(2),
|
|
Type_de_voie VARCHAR(50) NOT NULL,
|
|
Code_voie VARCHAR(10),
|
|
Voie VARCHAR(100),
|
|
Code_postal VARCHAR(5) NOT NULL,
|
|
Commune VARCHAR(50),
|
|
Code_departement VARCHAR(20),
|
|
Code_commune VARCHAR(10),
|
|
Code_type_local VARCHAR(1) NOT NULL,
|
|
Type_local VARCHAR(50) NOT NULL,
|
|
Surface_reelle_bati INT NOT NULL,
|
|
Nombre_pieces_principales INT NOT NULL,
|
|
Nature_culture VARCHAR(20) NOT NULL,
|
|
Nature_culture_speciale VARCHAR(20) NOT NULL,
|
|
Surface_terrain INT NOT NULL
|
|
);
|
|
|
|
DELETE FROM MACRO_DRAFT;
|
|
LOAD DATA LOCAL INFILE '/home/flyingscorpio/src/projet-bdd-dvf/valeursfoncieres-2021_sample.csv'
|
|
INTO TABLE MACRO_DRAFT
|
|
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (
|
|
@Code_service_CH,
|
|
@Reference_document,
|
|
@1_Articles_CGI,
|
|
@2_Articles_CGI,
|
|
@3_Articles_CGI,
|
|
@4_Articles_CGI,
|
|
@5_Articles_CGI,
|
|
@No_disposition,
|
|
@Date_mutation,
|
|
@Nature_mutation,
|
|
@Valeur_fonciere,
|
|
@No_voie,
|
|
@B_T_Q,
|
|
@Type_de_voie,
|
|
@Code_voie,
|
|
@Voie,
|
|
@Code_postal,
|
|
@Commune,
|
|
@Code_departement,
|
|
@Code_commune,
|
|
@Prefixe_de_section,
|
|
@Section,
|
|
@No_plan,
|
|
@No_Volume,
|
|
@1er_lot,
|
|
@Surface_Carrez_du_1er_lot,
|
|
@2eme_lot,
|
|
@Surface_Carrez_du_2eme_lot,
|
|
@3eme_lot,
|
|
@Surface_Carrez_du_3eme_lot,
|
|
@4eme_lot,
|
|
@Surface_Carrez_du_4eme_lot,
|
|
@5eme_lot,
|
|
@Surface_Carrez_du_5eme_lot,
|
|
@Nombre_de_lots,
|
|
@Code_type_local,
|
|
@Type_local,
|
|
@Identifiant_local,
|
|
@Surface_reelle_bati,
|
|
@Nombre_pieces_principales,
|
|
@Nature_culture,
|
|
@Nature_culture_speciale,
|
|
@Surface_terrain
|
|
)
|
|
SET
|
|
Date_mutation = STR_TO_DATE(@Date_mutation, '%d/%m/%Y'),
|
|
Nature_mutation = @Nature_mutation,
|
|
Valeur_fonciere = @Valeur_fonciere,
|
|
No_voie = @No_voie,
|
|
B_T_Q = @B_T_Q,
|
|
Type_de_voie = @Type_de_voie,
|
|
Code_voie = @Code_voie,
|
|
Voie = @Voie,
|
|
Code_postal = @Code_postal,
|
|
Commune = @Commune,
|
|
Code_departement = @Code_departement,
|
|
Code_commune = @Code_commune,
|
|
Code_type_local = @Code_type_local,
|
|
Type_local = @Type_local,
|
|
Surface_reelle_bati = @Surface_reelle_bati,
|
|
Nombre_pieces_principales = @Nombre_pieces_principales,
|
|
Nature_culture = @Nature_culture,
|
|
Nature_culture_speciale = @Nature_culture_speciale,
|
|
Surface_terrain = @Surface_terrain
|
|
;
|
|
|
|
-- vim: foldmethod=indent
|