Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
An SQL Query to insert 50 U.S. States into a database. Make sure your auto increment is set in MySQL, and Identity_insert is set in MS-SQL.
CREATE TABLE [state](
[stateID] [int] IDENTITY(1,1) NOT NULL,
[stateCode] [nchar](2) NOT NULL,
[stateName] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_state] PRIMARY KEY CLUSTERED
( [stateID] 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 [state] 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');
@drifterz28

This comment has been minimized.

Copy link

drifterz28 commented Jun 21, 2013

$states_abbr = array_flip(array("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", "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"));

@michalstanko

This comment has been minimized.

Copy link

michalstanko commented Apr 25, 2015

Thanks, but...Puerto Rico?

@FiestaUSA

This comment has been minimized.

Copy link

FiestaUSA commented Nov 1, 2015

michalstanko the title is all 50 states. Puerto Rico is not a state. You could have added Puerto Rico yourself in less time than it took to write your comment.

@originalmouse

This comment has been minimized.

Copy link

originalmouse commented Jan 12, 2016

the abreviation for Puerto Rico is PR (just in case anybody else wanted to know.)

@AllanJeremy

This comment has been minimized.

Copy link

AllanJeremy commented Nov 7, 2017

lmao @FiestaUSA, that response

@amikeliunas

This comment has been minimized.

Copy link

amikeliunas commented Nov 14, 2017

DC is not a state either, even though they wish they were.... but if we are talking shipping states we could also include the Virgin Islands.

@brett91ag

This comment has been minimized.

Copy link

brett91ag commented Dec 6, 2019

Alabama (AL) is missing

@netbizsystems

This comment has been minimized.

Copy link

netbizsystems commented Jan 6, 2020

If you are interested in data integrity, especially the kind that the DBMS provides, you should have a unique index on stateCode so that you can't possibly have two DCs (one is more than enough haha). Furthermore, stateCode could (and should IMHO) be the primary key. Why invent something when a natural key already exists.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.