State Table Script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

State Table Script

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-12 : 19:23:15

Anyone have a script to create a table of US States?
I'd rather not type in all 50 states if possible :)

Thanks!
The forums search page returned no such script :(

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-12 : 20:15:49

You gotta be kidding...

How about visiting [url]http://www.usps.com/[/url], they'll have a list of all the states, you can probably copy and paste it right off the screen (should also include Puerto Rico and the U.S. Territories too, like Guam)

Next you'll ask if you should use an identity column as a primary key in that table...

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-08-12 : 22:38:56

create table States (
State varchar(20))
go
insert into States
values ('Lazy')
insert into States
values ('Indifferent')
insert into States
values ('Grumpy')
insert into States
values ('Elated')
insert into States
values ('Sarcastic')
...

Come to think of it there is probably alot more than 50 states.

Ba-dum ching!

I'm here all week folks, the 11 o'clock show is totally different than the 8 o'clock show.

Justin

Have you hugged your SQL Server today?

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-13 : 07:26:20

Nope I didn't type them in
All 61 states from http://www.usps.com/cpim/ftp/pubs/pub201/yourmail.htm#abbr

insert tbl select 'Alabama'
insert tbl select 'Alaska'
insert tbl select 'American Samoa'
insert tbl select 'Arizona'
insert tbl select 'Arkansas'
insert tbl select 'California'
insert tbl select 'Colorado'
insert tbl select 'Connecticut'
insert tbl select 'Delaware'
insert tbl select 'District of Columbia'
insert tbl select 'Federated States of Micronesia'
insert tbl select 'Florida'
insert tbl select 'Georgia'
insert tbl select 'Guam'
insert tbl select 'Hawaii'
insert tbl select 'Idaho'
insert tbl select 'Illinois'
insert tbl select 'Indiana'
insert tbl select 'Iowa'
insert tbl select 'Kansas'
insert tbl select 'Kentucky'
insert tbl select 'Louisiana'
insert tbl select 'Maine'
insert tbl select 'Marshall Islands'
insert tbl select 'Maryland'
insert tbl select 'Massachusetts'
insert tbl select 'Michigan'
insert tbl select 'Minnesota'
insert tbl select 'Mississippi'
insert tbl select 'Missouri'
insert tbl select 'Montana'
insert tbl select 'Nebraska'
insert tbl select 'Nevada'
insert tbl select 'New Hampshire'
insert tbl select 'New Jersey'
insert tbl select 'New Mexico'
insert tbl select 'New York'
insert tbl select 'North Carolina'
insert tbl select 'North Dakota'
insert tbl select 'Northern Mariana Islands'
insert tbl select 'Ohio'
insert tbl select 'Oklahoma'
insert tbl select 'Oregon'
insert tbl select 'Pennsylvania'
insert tbl select 'Puerto Rico'
insert tbl select 'Rhode Island'
insert tbl select 'South Carolina'
insert tbl select 'South Dakota'
insert tbl select 'Tennessee'
insert tbl select 'Texas'
insert tbl select 'Utah'
insert tbl select 'Vermont'
insert tbl select 'Virginia'
insert tbl select 'Virgin Islands, U.S.'
insert tbl select 'Washington'
insert tbl select 'West Virginia'
insert tbl select 'Wisconsin'
insert tbl select 'Wyoming'
insert tbl select 'Armed Forces the Americas'
insert tbl select 'Armed Forces Europe'
insert tbl select 'Armed Forces Pacific'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-13 : 08:44:18

quote:

insert tbl select 'Federated States of Micronesia'
insert tbl select 'Marshall Islands'

Interesting: "in free association with the US" must include "gets a US postal code".

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-13 : 12:27:43

/me smacks head
USPS, now why didn't I think of that :)

Thanks all!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources