Monday, March 16, 2009

US states table for SQL Server

I couldn’t find any SQL to generate a table of US states for SQL Server though I found this for MySql, so I modified it slightly and came up with this for SQL Server. Create the table with this

CREATE TABLE States(
    StateCode char(2) NOT NULL,
    StateName varchar(250) NOT NULL,
 CONSTRAINT PK_States PRIMARY KEY CLUSTERED 
 (
    StateCode ASC
 )
)

And populate it with this

insert into States values ('AL', 'Alabama');
insert into States values ('AK', 'Alaska');
insert into States values ('AZ', 'Arizona');
insert into States values ('AR', 'Arkansas');
insert into States values ('CA', 'California');
insert into States values ('CO', 'Colorado');
insert into States values ('CT', 'Connecticut');
insert into States values ('DE', 'Delaware');
insert into States values ('DC', 'District of Columbia');
insert into States values ('FL', 'Florida');
insert into States values ('GA', 'Georgia');
insert into States values ('HI', 'Hawaii');
insert into States values ('ID', 'Idaho');
insert into States values ('IL', 'Illinois');
insert into States values ('IN', 'Indiana');
insert into States values ('IA', 'Iowa');
insert into States values ('KS', 'Kansas');
insert into States values ('KY', 'Kentucky');
insert into States values ('LA', 'Louisiana');
insert into States values ('ME', 'Maine');
insert into States values ('MD', 'Maryland');
insert into States values ('MA', 'Massachusetts');
insert into States values ('MI', 'Michigan');
insert into States values ('MN', 'Minnesota');
insert into States values ('MS', 'Mississippi');
insert into States values ('MO', 'Missouri');
insert into States values ('MT', 'Montana');
insert into States values ('NE', 'Nebraska');
insert into States values ('NV', 'Nevada');
insert into States values ('NH', 'New Hampshire');
insert into States values ('NJ', 'New Jersey');
insert into States values ('NM', 'New Mexico');
insert into States values ('NY', 'New York');
insert into States values ('NC', 'North Carolina');
insert into States values ('ND', 'North Dakota');
insert into States values ('OH', 'Ohio');
insert into States values ('OK', 'Oklahoma');
insert into States values ('OR', 'Oregon');
insert into States values ('PA', 'Pennsylvania');
insert into States values ('RI', 'Rhode Island');
insert into States values ('SC', 'South Carolina');
insert into States values ('SD', 'South Dakota');
insert into States values ('TN', 'Tennessee');
insert into States values ('TX', 'Texas');
insert into States values ('UT', 'Utah');
insert into States values ('VT', 'Vermont');
insert into States values ('VA', 'Virginia');
insert into States values ('WA', 'Washington');
insert into States values ('WV', 'West Virginia');
insert into States values ('WI', 'Wisconsin');
insert into States values ('WY', 'Wyoming');

11 comments:

Anonymous said...

Needs an ID field... abv. is well and great... but improper as far as data integrity goes.

Doogal Bell said...

That raises something of a philosophical point, should tables have ID fields when there is already a unique field that isn't numeric (the state code in this case). I just made the state code the primary key but I'd love to hear if there are compelling reasons for having an ID field

James Schubert said...

Some people create an id field for every table to avoid changes to the primary key. I doubt we'll see state codes change any time soon.

Thanks for posting this. I needed state codes and I didn't feel like compiling a list.

Anonymous said...

Woohoo! Thanks Doogal. And by the way, I've tried both having and not having a StateID - I like your method best (StateCode, StateName).

Anonymous said...

Thanks

Anonymous said...

Exactly what I needed... thanks!

Balrog said...

The USG apparently has. they use numeric state codes in these datasets. http://www.icpsr.umich.edu/icpsrweb/SAMHDA/studies/3088 . Fun to get Mappoint to sync up.

CK said...

Thanks for doing this. Just what I needed. Also prefer State Code as ID, they're unique ain't they? And if they do ever change you'll have to change your table anyway.

Anonymous said...

perfect, though i prefer numeric ids just in case you want to expand outside the us and end up with a non unique issue.

Anonymous said...

Thanks a bunch

Anonymous said...

thank you, this was very helpful - saved me a good 30 minutes. High 5!