Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL script to import traffic ticket data into MySQL database.
DROP DATABASE IF EXISTS violations;
CREATE DATABASE violations;
USE violations;
DROP TABLE IF EXISTS _2011;
CREATE table _2011 (
Violation_Charged_Code VARCHAR(30),
Violation_Description VARCHAR(80),
Violation_Month VARCHAR(5),
Violation_Time VARCHAR(12),
Age_at_Violation VARCHAR(3),
State_of_License VARCHAR(50),
Gender CHAR(2),
Police_Agency VARCHAR(80),
Court VARCHAR(80),
Source VARCHAR(30),
INDEX (Gender, Age_at_Violation, Police_Agency)
);
DROP TABLE IF EXISTS _2012;
CREATE table _2012 (
Violation_Charged_Code VARCHAR(30),
Violation_Description VARCHAR(80),
Violation_Month VARCHAR(5),
Violation_Time VARCHAR(12),
Age_at_Violation VARCHAR(3),
State_of_License VARCHAR(50),
Gender CHAR(2),
Police_Agency VARCHAR(80),
Court VARCHAR(80),
Source VARCHAR(30),
INDEX (Gender, Age_at_Violation, Police_Agency)
);
LOAD DATA INFILE '/tmp/violations2011.csv' INTO TABLE _2011
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(Violation_Charged_Code,Violation_Description,Violation_Month,Violation_Time,Age_at_Violation,State_of_License,Gender,Police_Agency,Court,Source);
LOAD DATA INFILE '/tmp/violations2012.csv' INTO TABLE _2012
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(Violation_Charged_Code,Violation_Description,Violation_Month,Violation_Time,Age_at_Violation,State_of_License,Gender,Police_Agency,Court,Source);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment