Using this script you can insert the create a table for locations and insert the US states in it.

First create the locations table

CREATE TABLE [dbo].[Locations](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[Location] [varchar](50) NULL,
[Abbrev] [varchar](3) NULL
CONSTRAINT [PK_Locations] PRIMARY KEY CLUSTERED
(
[LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Now you can insert the US states using this script

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

From Webcosmo Webmaster Forum http://www.webcosmoforums.com/databases/21515-insert-script-us-states-abbreviation-ms-sql.html