I recently needed a lookup table of all 50 US states (and Washington DC) and the provinces / territories in Canada. The script below will create a table (tbl_LUState) and will populate the table with the data.

/****** Object:  Index [tbl_LUState$StateAbbreviation]    Script Date: 10/22/2014 12:57:17 PM ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_LUState]') AND name = N'tbl_LUState$StateAbbreviation')
DROP INDEX [tbl_LUState$StateAbbreviation] ON [dbo].[tbl_LUState]
GO
/****** Object:  Table [dbo].[tbl_LUState]    Script Date: 10/22/2014 12:57:17 PM ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_LUState]') AND type in (N'U'))
DROP TABLE [dbo].[tbl_LUState]
GO
/****** Object:  Table [dbo].[tbl_LUState]    Script Date: 10/22/2014 12:57:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tbl_LUState]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tbl_LUState](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StateName] [varchar](50) NULL,
    [StateAbbreviation] [varchar](5) NULL,
    [Country] [varchar](10) NULL,
    [Type] [varchar](10) NULL,
 CONSTRAINT [PK_tbl_LUState] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tbl_LUState] ON

INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (1, N'Alabama', N'AL', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (2, N'Alaska', N'AK', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (3, N'Arizona', N'AZ', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (4, N'Arkansas', N'AR', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (5, N'California', N'CA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (6, N'Colorado', N'CO', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (7, N'Connecticut', N'CT', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (8, N'Delaware', N'DE', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (9, N'Florida', N'FL', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (10, N'Georgia', N'GA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (11, N'Hawaii', N'HI', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (12, N'Idaho', N'ID', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (13, N'Illinois', N'IL', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (14, N'Indiana', N'IN', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (15, N'Iowa', N'IA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (16, N'Kansas', N'KS', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (17, N'Kentucky', N'KY', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (18, N'Louisiana', N'LA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (19, N'Maine', N'ME', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (20, N'Maryland', N'MD', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (21, N'Massachusetts', N'MA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (22, N'Michigan', N'MI', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (23, N'Minnesota', N'MN', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (24, N'Mississippi', N'MS', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (25, N'Missouri', N'MO', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (26, N'Montana', N'MT', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (27, N'Nebraska', N'NE', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (28, N'Nevada', N'NV', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (29, N'New Hampshire', N'NH', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (30, N'New Jersey', N'NJ', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (31, N'New Mexico', N'NM', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (32, N'New York', N'NY', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (33, N'North Carolina', N'NC', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (34, N'North Dakota', N'ND', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (35, N'Ohio', N'OH', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (36, N'Oklahoma', N'OK', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (37, N'Oregon', N'OR', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (38, N'Pennsylvania', N'PA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (39, N'Rhode Island', N'RI', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (40, N'South Carolina', N'SC', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (41, N'South Dakota', N'SD', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (42, N'Tennessee', N'TN', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (43, N'Texas', N'TX', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (44, N'Utah', N'UT', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (45, N'Vermont', N'VT', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (46, N'Virginia', N'VA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (47, N'Washington', N'WA', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (48, N'West Virginia', N'WV', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (49, N'Wisconsin', N'WI', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (50, N'Wyoming', N'WY', N'USA', N'state')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (51, N'Washington DC', N'DC', N'USA', N'capitol')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (52, N'Alberta', N'AB', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (53, N'British Columbia', N'BC', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (54, N'Manitoba', N'MB', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (55, N'New Brunswick', N'NB', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (56, N'Newfoundland and Labrador', N'NL', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (57, N'Nova Scotia', N'NS', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (58, N'Ontario', N'ON', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (59, N'Prince Edward Island', N'PE', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (60, N'Quebec', N'QC', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (61, N'Saskatchewan', N'SK', N'Canada', N'province')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (62, N'Northwest Territories', N'NT', N'Canada', N'territory')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (63, N'Nunavut', N'NU', N'Canada', N'territory')
INSERT [dbo].[tbl_LUState] ([ID], [StateName], [StateAbbreviation], [Country], [Type]) VALUES (64, N'Yukon Territory', N'YT', N'Canada', N'territory')
SET IDENTITY_INSERT [dbo].[tbl_LUState] OFF
SET ANSI_PADDING ON

GO
/****** Object:  Index [tbl_LUState$StateAbbreviation]    Script Date: 10/22/2014 12:57:17 PM ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tbl_LUState]') AND name = N'tbl_LUState$StateAbbreviation')
CREATE NONCLUSTERED INDEX [tbl_LUState$StateAbbreviation] ON [dbo].[tbl_LUState]
(
    [StateAbbreviation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The script checks for the object’s existence and drops / creates it if it exists. I also threw an index on StateAbbreviation since that is the column we lookup date on most often. You can adjust those as you see fit.

Related Posts

%d bloggers like this: