Share This!

Wednesday, May 28, 2008

Easy SQL to Insert the states and territories into a Lookup Table

This has been tested in MSSQL.
truncate table states;

--STATES
INSERT INTO states (state, st) VALUES('ALABAMA','AL');
INSERT INTO states (state, st) VALUES('ALASKA','AK');
INSERT INTO states (state, st) VALUES('ARIZONA','AZ');
INSERT INTO states (state, st) VALUES('ARKANSAS','AR');
INSERT INTO states (state, st) VALUES('CALIFORNIA','CA');
INSERT INTO states (state, st) VALUES('COLORADO','CO');
INSERT INTO states (state, st) VALUES('CONNECTICUT','CT');
INSERT INTO states (state, st) VALUES('DELAWARE','DE');
INSERT INTO states (state, st) VALUES('DISTRICT OF COLUMBIA','DC');
INSERT INTO states (state, st) VALUES('FLORIDA','FL');
INSERT INTO states (state, st) VALUES('GEORGIA','GA');
INSERT INTO states (state, st) VALUES('HAWAII','HI');
INSERT INTO states (state, st) VALUES('IDAHO','ID');
INSERT INTO states (state, st) VALUES('ILLINOIS','IL');
INSERT INTO states (state, st) VALUES('INDIANA','IN');
INSERT INTO states (state, st) VALUES('IOWA','IA');
INSERT INTO states (state, st) VALUES('KANSAS','KS');
INSERT INTO states (state, st) VALUES('KENTUCKY','KY');
INSERT INTO states (state, st) VALUES('LOUISIANA','LA');
INSERT INTO states (state, st) VALUES('MAINE','ME');
INSERT INTO states (state, st) VALUES('MARYLAND','MD');
INSERT INTO states (state, st) VALUES('MASSACHUSETTS','MA');
INSERT INTO states (state, st) VALUES('MICHIGAN','MI');
INSERT INTO states (state, st) VALUES('MINNESOTA','MN');
INSERT INTO states (state, st) VALUES('MISSISSIPPI','MS');
INSERT INTO states (state, st) VALUES('MISSOURI','MO');
INSERT INTO states (state, st) VALUES('MONTANA','MT');
INSERT INTO states (state, st) VALUES('NEBRASKA','NE');
INSERT INTO states (state, st) VALUES('NEVADA','NV');
INSERT INTO states (state, st) VALUES('NEW HAMPSHIRE','NH');
INSERT INTO states (state, st) VALUES('NEW JERSEY','NJ');
INSERT INTO states (state, st) VALUES('NEW MEXICO','NM');
INSERT INTO states (state, st) VALUES('NEW YORK','NY');
INSERT INTO states (state, st) VALUES('NORTH CAROLINA','NC');
INSERT INTO states (state, st) VALUES('NORTH DAKOTA','ND');
INSERT INTO states (state, st) VALUES('OHIO','OH');
INSERT INTO states (state, st) VALUES('OKLAHOMA','OK');
INSERT INTO states (state, st) VALUES('OREGON','OR');
INSERT INTO states (state, st) VALUES('PENNSYLVANIA','PA');
INSERT INTO states (state, st) VALUES('RHODE ISLAND','RI');
INSERT INTO states (state, st) VALUES('SOUTH CAROLINA','SC');
INSERT INTO states (state, st) VALUES('SOUTH DAKOTA','SD');
INSERT INTO states (state, st) VALUES('TENNESSEE','TN');
INSERT INTO states (state, st) VALUES('TEXAS','TX');
INSERT INTO states (state, st) VALUES('UTAH','UT');
INSERT INTO states (state, st) VALUES('VERMONT','VT');
INSERT INTO states (state, st) VALUES('VIRGINIA','VA');
INSERT INTO states (state, st) VALUES('WASHINGTON','WA');
INSERT INTO states (state, st) VALUES('WEST VIRGINIA','WV');
INSERT INTO states (state, st) VALUES('WISCONSIN','WI');
INSERT INTO states (state, st) VALUES('WYOMING','WY');

--TERRITORIES
INSERT INTO states (state, st) VALUES('AMERICAN SAMOA','AS');
INSERT INTO states (state, st) VALUES('FEDERATED STATES OF MICRONESIA','FM');
INSERT INTO states (state, st) VALUES('GUAM','GU');
INSERT INTO states (state, st) VALUES('MARSHALL ISLANDS','MH');
INSERT INTO states (state, st) VALUES('NORTHERN MARIANA ISLANDS','MP');
INSERT INTO states (state, st) VALUES('PALAU','PW');
INSERT INTO states (state, st) VALUES('PUERTO RICO','PR');
INSERT INTO states (state, st) VALUES('VIRGIN ISLANDS','VI');

--CANADA


INSERT INTO states (state, st) VALUES('Alberta','AB');
INSERT INTO states (state, st) VALUES('British Columbia','BC');
INSERT INTO states (state, st) VALUES('Manitoba','MB');
INSERT INTO states (state, st) VALUES('New Brunswick','NB');
INSERT INTO states (state, st) VALUES('Newfoundland and Labrador','NL');
INSERT INTO states (state, st) VALUES('Northwest Territories','NT');
INSERT INTO states (state, st) VALUES('Nova Scotia','NS');
INSERT INTO states (state, st) VALUES('Nunavut','NU');
INSERT INTO states (state, st) VALUES('Ontario','ON');
INSERT INTO states (state, st) VALUES('Prince Edward Island','PE');
INSERT INTO states (state, st) VALUES('Quebec','QC');
INSERT INTO states (state, st) VALUES('Saskatchewan','SK');
INSERT INTO states (state, st) VALUES('Yukon','YT');

No comments:

Post a Comment

Contact Us

Name

Email *

Message *