Tuesday, April 5, 2016

Province Lookup Table

On occasion I've needed a lookup table for states and provinces. The following DDL and DML are my typical starting point. You can adjust it as needed. The second scrip includes states and provinces for the U.S., Canada and Australia.  ( see Country Code Dimension or Lookup Table)

DDL for the Province table:

       

use yourdatabase;
go


if exists (select * from sys.objects where object_id = object_id(N'dbo.Province'))
  drop table dbo.Province
  go

 CREATE TABLE dbo.Province
 (
  ProvinceKey  smallint  IDENTITY (1,1) ,
  CountryKey  smallint not null,    -- FK  
  ProvinceCode  nvarchar(50)  NOT NULL ,
  ProvinceLongName  nvarchar(100) not NULL ,
  ProvinceShortName    nvarchar(50)  NULL ,
  ProvinceDesc  nvarchar(255)  NULL ,     
  SortOrder  smallint  NOT NULL ,
  AuditKey int  NOT NULL ,
  CONSTRAINT  PK_Province PRIMARY KEY (ProvinceKey  ASC),
  --  CONSTRAINT  FK_Province_Country_CountryKey FOREIGN KEY (CountryKey) REFERENCES  dbo.Country(CountryKey)  
 )
 go

  -- AK
  CREATE  UNIQUE INDEX AK_ProvinceProvinceCode ON dbo.Province ( CountryKey, ProvinceCode  ASC  )
  go
  CREATE  UNIQUE INDEX AK_ProvinceProvince_Long_Name ON dbo.Province ( CountryKey, ProvinceLongName  ASC)
  go
  CREATE  UNIQUE INDEX AK_ProvinceProvince_Short_Name ON dbo.Province ( CountryKey, ProvinceShortName  ASC)
  go


DML to initially populate the table: 

       

/*  -------------------------------------

 ----------------------------
 Province Populate Listing
 ----------------------------
 - country table required
 - Countries SPECIFIC MAP BY ID

    ------------------------------------- */
use yourdatabase;
go


   
SET IDENTITY_INSERT [dbo].[Province] ON
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (1, 8, N'AB', N'Alberta', N'AB', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (4, 8, N'BC', N'British Columbia', N'BC', N'', 20, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (5, 8, N'MB', N'Manitoba', N'MB', N'', 30, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (6, 8, N'NB', N'New Brunswick', N'NB', N'', 40, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (7, 8, N'NL', N'Newfoundland and Labrado', N'NL', N'', 50, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (8, 8, N'NS', N'Nova Scotia', N'NS', N'', 60, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (9, 8, N'NU', N'Nunavut', N'NU', N'', 70, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (10, 8, N'ON', N'Ontario', N'ON', N'', 80, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (11, 8, N'PE', N'Prince Edward Island', N'PE', N'', 90, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (12, 8, N'QC', N'Quebec', N'QC', N'', 100, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (13, 8, N'SK', N'Saskatchewan', N'SK', N'', 110, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (14, 8, N'YT', N'Yukon Territory', N'Yukon', N'', 120, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (15, 1, N'ACT', N'Australian Capital Territory', N'Aus Capital', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (16, 1, N'QLD', N'Queensland', N'Queensland', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (17, 1, N'VIC', N'Victoria', N'Victoria', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (18, 1, N'NSW', N'New South Wales', N'NS Wales', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (19, 1, N'SA', N'South Australia', N'S. Aus.', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (20, 1, N'WA', N'Western Australia', N'W. Aus.', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (21, 1, N'NT', N'Northern Territory', N'N. Terr.', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (22, 1, N'TAS', N'Tasmania', N'Tasmania', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (23, 4, N'Scotland', N'Scotland', N'Scotland', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (24, 4, N'England', N'England', N'England', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (25, 4, N'Wales', N'Wales', N'Wales', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (26, 4, N'Northern Ireland', N'Northern Ireland', N'N. Ireland', N'', 10, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (27, 2, N'AK', N'Alaska', N'Alaska', N'', 270, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (28, 2, N'AL', N'Alabama', N'Alabama', N'', 280, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (29, 2, N'AR', N'Arkansas', N'Arkansas', N'', 290, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (30, 2, N'AZ', N'Arizona', N'Arizona', N'', 300, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (31, 2, N'CA', N'California', N'California', N'', 310, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (32, 2, N'CO', N'Colorado', N'Colorado', N'', 320, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (33, 2, N'CT', N'Connecticut', N'Connecticut', N'', 330, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (34, 2, N'DC', N'District of Columbia', N'District of Columbia', N'', 340, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (35, 2, N'DE', N'Delaware', N'Delaware', N'', 350, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (36, 2, N'FL', N'Florida', N'Florida', N'', 360, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (37, 2, N'GA', N'Georgia', N'Georgia', N'', 370, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (38, 2, N'HI', N'Hawaii', N'Hawaii', N'', 380, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (39, 2, N'IA', N'Iowa', N'Iowa', N'', 390, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (40, 2, N'ID', N'Idaho', N'Idaho', N'', 400, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (41, 2, N'IL', N'Illinois', N'Illinois', N'', 410, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (42, 2, N'IN', N'Indiana', N'Indiana', N'', 420, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (43, 2, N'KS', N'Kansas', N'Kansas', N'', 430, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (44, 2, N'KY', N'Kentucky', N'Kentucky', N'', 440, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (45, 2, N'LA', N'Louisiana', N'Louisiana', N'', 450, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (46, 2, N'MA', N'Massachusetts', N'Massachusetts', N'', 460, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (47, 2, N'MD', N'Maryland', N'Maryland', N'', 470, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (48, 2, N'ME', N'Maine', N'Maine', N'', 480, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (49, 2, N'MI', N'Michigan', N'Michigan', N'', 490, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (50, 2, N'MN', N'Minnesota', N'Minnesota', N'', 500, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (51, 2, N'MO', N'Missouri', N'Missouri', N'', 510, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (52, 2, N'MS', N'Mississippi', N'Mississippi', N'', 520, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (53, 2, N'MT', N'Montana', N'Montana', N'', 530, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (54, 2, N'NC', N'North Carolina', N'North Carolina', N'', 540, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (55, 2, N'ND', N'North Dakota', N'North Dakota', N'', 550, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (56, 2, N'NE', N'Nebraska', N'Nebraska', N'', 560, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (57, 2, N'NH', N'New Hampshire', N'New Hampshire', N'', 570, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (58, 2, N'NJ', N'New Jersey', N'New Jersey', N'', 580, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (59, 2, N'NM', N'New Mexico', N'New Mexico', N'', 590, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (60, 2, N'NV', N'Nevada', N'Nevada', N'', 600, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (61, 2, N'NY', N'New York', N'New York', N'', 610, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (62, 2, N'OH', N'Ohio', N'Ohio', N'', 620, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (63, 2, N'OK', N'Oklahoma', N'Oklahoma', N'', 630, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (64, 2, N'OR', N'Oregon', N'Oregon', N'', 640, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (65, 2, N'PA', N'Pennsylvania', N'Pennsylvania', N'', 650, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (66, 2, N'RI', N'Rhode Island', N'Rhode Island', N'', 660, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (67, 2, N'SC', N'South Carolina', N'South Carolina', N'', 670, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (68, 2, N'SD', N'South Dakota', N'South Dakota', N'', 680, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (69, 2, N'TN', N'Tennessee', N'Tennessee', N'', 690, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (70, 2, N'TX', N'Texas', N'Texas', N'', 700, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (71, 2, N'UT', N'Utah', N'Utah', N'', 710, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (72, 2, N'VA', N'Virginia', N'Virginia', N'', 720, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (73, 2, N'VT', N'Vermont', N'Vermont', N'', 730, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (74, 2, N'WA', N'Washington', N'Washington', N'', 740, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (75, 2, N'WI', N'Wisconsin', N'Wisconsin', N'', 750, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (76, 2, N'WV', N'West Virginia', N'West Virginia', N'', 760, 1)
INSERT [dbo].[Province] ([ProvinceKey], [CountryKey], [ProvinceCode], [ProvinceLongName], [ProvinceShortName], [ProvinceDesc], [SortOrder], [AuditKey]) VALUES (77, 2, N'WY', N'Wyoming', N'Wyoming', N'', 770, 1)
SET IDENTITY_INSERT [dbo].[Province] OFF
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)