If you should ever need to get a list of U.S. States and their USPS 2 letter abbreviation into a SQL database, here’s some code that’ll do it for you.
Notes: This is tested to work with MS SQL 2005, it assumes you have a table already created called tblStates and that table has two columns, State and Abbr (both having a data type and length that will accommodate the data).
INSERT INTO tblStates (State, Abbr) VALUES (‘Alabama’,’AL’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Alaska’,’AK’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Arizona’,’AZ’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Arkansas’,’AR’);
INSERT INTO tblStates (State, Abbr) VALUES (‘California’,’CA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Colorado’,’CO’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Connecticut’,’CT’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Delaware’,’DE’);
INSERT INTO tblStates (State, Abbr) VALUES (‘District of Columbia’,’DC’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Florida’,’FL’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Georgia’,’GA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Hawaii’,’HI’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Idaho’,’ID’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Illinois’,’IL’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Indiana’,’IN’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Iowa’,’IA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Kansas’,’KS’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Kentucky’,’KY’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Louisiana’,’LA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Maine’,’ME’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Maryland’,’MD’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Massachusetts’,’MA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Michigan’,’MI’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Minnesota’,’MN’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Mississippi’,’MS’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Missouri’,’MO’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Montana’,’MT’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Nebraska’,’NE’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Nevada’,’NV’);
INSERT INTO tblStates (State, Abbr) VALUES (‘New Hampshire’,’NH’);
INSERT INTO tblStates (State, Abbr) VALUES (‘New Jersey’,’NJ’);
INSERT INTO tblStates (State, Abbr) VALUES (‘New Mexico’,’NM’);
INSERT INTO tblStates (State, Abbr) VALUES (‘New York’,’NY’);
INSERT INTO tblStates (State, Abbr) VALUES (‘North Carolina’,’NC’);
INSERT INTO tblStates (State, Abbr) VALUES (‘North Dakota’,’ND’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Ohio’,’OH’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Oklahoma’,’OK’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Oregon’,’OR’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Pennsylvania’,’PA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Puerto Rico’,’PR’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Rhode Island’,’RI’);
INSERT INTO tblStates (State, Abbr) VALUES (‘South Carolina’,’SC’);
INSERT INTO tblStates (State, Abbr) VALUES (‘South Dakota’,’SD’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Tennessee’,’TN’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Texas’,’TX’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Utah’,’UT’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Vermont’,’VT’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Virginia’,’VA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Washington’,’WA’);
INSERT INTO tblStates (State, Abbr) VALUES (‘West Virginia’,’WV’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Wisconsin’,’WI’);
INSERT INTO tblStates (State, Abbr) VALUES (‘Wyoming’,’WY’);
I’m sure there’s a more efficient way to do this, but in the meantime, there you go!
