Copyright (C) 2006 Quentin Sager Consulting Inc
All rights reserved.
www.quentinsagerconsulting.com

CREATE DATABASE zipgold;

CREATE TABLE zipcodes (
    ZIP char(5) not null,
    CITY varchar(28),
    COUNTY varchar(25),
    STATENAME varchar(28),
    STATE char(2),
    CITYTYPE char(1),
    ZIPTYPE char(1),
    FACILITYTYPE char(1),
    DELIVERY char(1),
    MERGE char(1),
    CSA char(3),
    CBSA char(5),
    CBSA_DIV char(5),
    CBSATYPE char(1),
    MSA char(4),
    PMSA char(4),
    MSA_FLAG char(1),
    FIPS_STATE char(2),
    FIPS_COUNTY char(3),
    AREACODE varchar(64),
    GMT char(2),
    DST char(1),
    LATITUDE double,
    LONGITUDE double);

CREATE TABLE county (
    FIPS_STATE char(2) not null,
    FIPS_COUNTY char(3) not null,
    COUNTY varchar(25),
    STATE char(2),
    COUNTY_TYPE char(1),
    POPULATION double,
    LA_MILES double,
    WA_MILES double,
    NAME_TYPE char(1));

CREATE TABLE csa (
    CSA char(3) not null,
    AREA_NAME varchar(128),
    STATE varchar(28));

CREATE TABLE cbsa (
    CBSA char(5) not null,
    CSA char(3),
    AREA_NAME varchar(128),
    STATE varchar(28),
    AREA_TYPE varchar(28));

CREATE TABLE cbsadiv (
    CBSA_DIVISION char(5) not null,
    CBSA char(5),
    CSA char(3),
    AREA_NAME varchar(128),
    STATE varchar(28),
    AREA_TYPE varchar(28));

CREATE TABLE msa (
    MSA char(4) not null,
    PMSA char(4),
    ALT_CMSA char(2),
    AREA_NAME varchar(128),
    STATE varchar(28),
    AREA_TYPE varchar(10));


LOAD DATA LOCAL INFILE 'zipgold.csv' INTO TABLE zipcodes FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'fips.csv' INTO TABLE county FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'csa.csv' INTO TABLE csa FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'cbsa.csv' INTO TABLE cbsa FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'cbsadiv.csv' INTO TABLE cbsadiv FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
LOAD DATA LOCAL INFILE 'msa.csv' INTO TABLE msa FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;