start transaction;


create table whitepages
(rcdid INTEGER,
home NVARCHAR(250) NULL,
lastname NVARCHAR(30),
firstname NVARCHAR(30) NULL,
middlename NVARCHAR(20) NULL,
phone NVARCHAR(30) ,
outsidephone NVARCHAR(5) NULL,
fax NVARCHAR(30) NULL,
outsidefax NVARCHAR(5) NULL,
department NVARCHAR(255) NULL,
campus NVARCHAR(20) NULL,
title NVARCHAR(255) NULL,
discipline NVARCHAR(250) NULL,
bldg NVARCHAR(13) NULL,
rm NVARCHAR(25) NULL,
Email NVARCHAR(30) NULL,
mailingaddress1 NVARCHAR(250) NULL,
mailingaddress2 NVARCHAR(250) NULL,
mailingaddress3 NVARCHAR(250) NULL,
mailingaddress4 NVARCHAR(250) NULL,
mailingaddress5 NVARCHAR(250) NULL,
officecode NVARCHAR(5) NULL,
keywords NVARCHAR(500) NULL);




LOAD DATA LOCAL INFILE '/tmp/peopleSearch.txt'
INTO TABLE whitepages
FIELDS TERMINATED BY '\t'
LINES  TERMINATED BY '\r\n' ;

/*
alter table whitepages add metaphone_home varchar(100);
alter table whitepages add metaphone_lastname varchar(100);
alter table whitepages add metaphone_firstname varchar(100);
alter table whitepages add metaphone_middlename varchar(100);
alter table whitepages add metaphone_phone varchar(100);
alter table whitepages add metaphone_outsidephone varchar(100);
alter table whitepages add metaphone_fax varchar(100);
alter table whitepages add metaphone_outsidefax varchar(100);
alter table whitepages add metaphone_department varchar(100);
alter table whitepages add metaphone_campus varchar(100);
alter table whitepages add metaphone_title varchar(100);
alter table whitepages add metaphone_discipline varchar(100);
alter table whitepages add metaphone_bldg varchar(100);
alter table whitepages add metaphone_rm varchar(100);
alter table whitepages add metaphone_Email varchar(100);
alter table whitepages add metaphone_mailingaddress1 varchar(100);
alter table whitepages add metaphone_mailingaddress2 varchar(100);
alter table whitepages add metaphone_mailingaddress3 varchar(100);
alter table whitepages add metaphone_mailingaddress4 varchar(100);
alter table whitepages add metaphone_mailingaddress5 varchar(100);
alter table whitepages add metaphone_officecode varchar(100);
alter table whitepages add metaphone_keywords varchar(100);
*/


create table yellowpages
(rcdid integer,
home nvarchar(75) null,
department nvarchar(100),
office nvarchar(75) null,
campus nvarchar(20) null,
phone nvarchar(20) not null,
outsidephone nvarchar(5) null,
bldg nvarchar(15) null,
rm nvarchar(5) null,
officecode nvarchar(5) null,
keywords nvarchar(500) null);

LOAD DATA LOCAL INFILE '/tmp/deptSearch.txt'
INTO TABLE yellowpages
FIELDS TERMINATED BY '\t'
LINES  TERMINATED BY '\r\n' ;

/*
alter table yellowpages add metaphone_home varchar(100);
alter table yellowpages add metaphone_department varchar(100);
alter table yellowpages add metaphone_office varchar(100);
alter table yellowpages add metaphone_campus varchar(100);
alter table yellowpages add metaphone_phone varchar(100);
alter table yellowpages add metaphone_outsidephone varchar(100);
alter table yellowpages add metaphone_bldg varchar(100);
alter table yellowpages add metaphone_rm varchar(100);
alter table yellowpages add metaphone_officecode varchar(100);
alter table yellowpages add metaphone_keywords varchar(100);
*/

commit;

