Populate States Table

Posted on May 10, 2015

I always find myself recreating a States table for web applications. Here it is.

CREATE TABLE [States](

[StatesID] [int] IDENTITY(1,1) NOT NULL,
[Abbreviation] [nchar](2) NOT NULL,
[Name] [nvarchar](128) NOT NULL,

CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
( [StatesID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]

GO

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