30 Dec, 2024

Latest News

5 Ways to Spring Clean Your Security

Type conversion in expression CONVERT_IMPLICIT may affect “SeekPlan” in query plan choice.

Insert if missing, example of joining tables to exclude duplicates.

PDF viewer window caption

Datalogic Devices on Windows 10 and issues with Windows Mobile Device Center (WMDC) 6.1

States / Provinces Lookup Table

2014-10-222020-06-14 Brad Arnold

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