Skip to content

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
You can’t perform that action at this time.