Thread: Database Load COmmand

The database load command has two functions:

1)     It will be used to populate database tables from CSV files

2)     It will be used to update database tables from CSV files

The command might be run in Sun Solaris (sparc) or in Windows 2000

machine. We are assuming that will not get local access to their database

server, the access is probably happen over JDBC or/and ODBC.

Database will be Microsoft SQL Server 2000 SP3.

Please note that a updated CSV file will be loaded to the database

once a month (the function 2) above). The load command should

not duplicate existing rows in tables, and it should not cause

production downtime, i.e. it must not just delete all existing rows

(or drop existing table) and create all rows from scratch as

that kind of update would cause production downtime.

I have included few lines from beginning of two of the CSV files.

As you can see, the first line in CSV file lists the column names.

The corresponding database tables will have same column names to

make things easier. All columns won’t be VARCHARs, some

of them will be floating point numbers (longitude and latitude must

be floating point numbers in the tables).

--- postalcode.csv ---

"POSTAL_CODE","CITY","PROVINCE","PROVINCE_ABBR","AREA_CODE","CITY_FLAG","TIME_ZONE","DAY_LIGHT_SAVING","LATITUDE","LONGITUDE"

"A0A 1A0","AQUAFORTE","NEWFOUNDLAND","NF","709","N","3.5","Y","47.007050","-52.95195"

"A0A 1B0","AVONDALE","NEWFOUNDLAND","NF","709","N","3.5","Y","47.416880","-53.19755"

"A0A 1C0","BAY BULLS","NEWFOUNDLAND","NF","709","N","3.5","Y","47.321210","-52.81743"

"A0A 1E0","BAY DE VERDE","NEWFOUNDLAND","NF","709","N","3.5","Y","48.086930","-52.89581"

--- postalcode.csv ---

--- zipcode.csv ---

"ZIP_CODE","CITY","STATE","AREA_CODE","CITY_ALIAS_NAME","CITY_ALIAS_ABBR","CITY_TYPE","COUNTY_NAME","COUNTY_FIPS","TIME_ZONE","DAY_LIGHT_SAVING","LATITUDE","LONGITUDE","ELEVATION"

"00501","HOLTSVILLE","NY","516","I R S SERVICE CENTER","","N","SUFFOLK","103","5","Y","40.81518","-73.0455","25"

"00501","HOLTSVILLE","NY","516","HOLTSVILLE","","P","SUFFOLK","103","5","Y","40.81518","-73.0455","25"