Friday, January 28, 2011

T-SQL to Populate a Table of Country Codes with ISO 3166-1 Alpha-2 Codes and Names

You can use this query to populate a table with 2 character alpha country codes and their corresponding names.

/*************************************************************

Country codes taken from WikiPedia and conform to ISO 3166-1 Alpha-2
http://en.wikipedia.org/wiki/ISO_3166-1_alpha-2#Officially_assigned_code_elements

**************************************************************/


INSERT CountryCode (CountryCD, CountryName)
SELECT 'AD', 'Andorra' UNION ALL
SELECT 'AE', 'United Arab Emirates' UNION ALL
SELECT 'AF', 'Afghanistan' UNION ALL
SELECT 'AG', 'Antigua and Barbuda' UNION ALL
SELECT 'AI', 'Anguilla' UNION ALL
SELECT 'AL', 'Albania' UNION ALL
SELECT 'AM', 'Armenia' UNION ALL
SELECT 'AO', 'Angola' UNION ALL
SELECT 'AQ', 'Antarctica' UNION ALL
SELECT 'AR', 'Argentina' UNION ALL
SELECT 'AS', 'American Samoa' UNION ALL
SELECT 'AT', 'Austria' UNION ALL
SELECT 'AU', 'Australia' UNION ALL
SELECT 'AW', 'Aruba' UNION ALL
SELECT 'AX', 'land Islands' UNION ALL
SELECT 'AZ', 'Azerbaijan' UNION ALL
SELECT 'BA', 'Bosnia and Herzegovina' UNION ALL
SELECT 'BB', 'Barbados' UNION ALL
SELECT 'BD', 'Bangladesh' UNION ALL
SELECT 'BE', 'Belgium' UNION ALL
SELECT 'BF', 'Burkina Faso' UNION ALL
SELECT 'BG', 'Bulgaria' UNION ALL
SELECT 'BH', 'Bahrain' UNION ALL
SELECT 'BI', 'Burundi' UNION ALL
SELECT 'BJ', 'Benin' UNION ALL
SELECT 'BL', 'Saint Barth‚lemy' UNION ALL
SELECT 'BM', 'Bermuda' UNION ALL
SELECT 'BN', 'Brunei Darussalam' UNION ALL
SELECT 'BO', '"Bolivia, Plurinational State of"' UNION ALL
SELECT 'BQ', '"Bonaire, Saint Eustatius and Saba"' UNION ALL
SELECT 'BR', 'Brazil' UNION ALL
SELECT 'BS', 'Bahamas' UNION ALL
SELECT 'BT', 'Bhutan' UNION ALL
SELECT 'BV', 'Bouvet Island' UNION ALL
SELECT 'BW', 'Botswana' UNION ALL
SELECT 'BY', 'Belarus' UNION ALL
SELECT 'BZ', 'Belize' UNION ALL
SELECT 'CA', 'Canada' UNION ALL
SELECT 'CC', 'Cocos (Keeling) Islands' UNION ALL
SELECT 'CD', '"Congo, the Democratic Republic of the"' UNION ALL
SELECT 'CF', 'Central African Republic' UNION ALL
SELECT 'CG', 'Congo' UNION ALL
SELECT 'CH', 'Switzerland' UNION ALL
SELECT 'CI', 'Cote d`Ivoire' UNION ALL
SELECT 'CK', 'Cook Islands' UNION ALL
SELECT 'CL', 'Chile' UNION ALL
SELECT 'CM', 'Cameroon' UNION ALL
SELECT 'CN', 'China' UNION ALL
SELECT 'CO', 'Colombia' UNION ALL
SELECT 'CR', 'Costa Rica' UNION ALL
SELECT 'CU', 'Cuba' UNION ALL
SELECT 'CV', 'Cape Verde' UNION ALL
SELECT 'CW', 'Cura‡ao' UNION ALL
SELECT 'CX', 'Christmas Island' UNION ALL
SELECT 'CY', 'Cyprus' UNION ALL
SELECT 'CZ', 'Czech Republic' UNION ALL
SELECT 'DE', 'Germany' UNION ALL
SELECT 'DJ', 'Djibouti' UNION ALL
SELECT 'DK', 'Denmark' UNION ALL
SELECT 'DM', 'Dominica' UNION ALL
SELECT 'DO', 'Dominican Republic' UNION ALL
SELECT 'DZ', 'Algeria' UNION ALL
SELECT 'EC', 'Ecuador' UNION ALL
SELECT 'EE', 'Estonia' UNION ALL
SELECT 'EG', 'Egypt' UNION ALL
SELECT 'EH', 'Western Sahara' UNION ALL
SELECT 'ER', 'Eritrea' UNION ALL
SELECT 'ES', 'Spain' UNION ALL
SELECT 'ET', 'Ethiopia' UNION ALL
SELECT 'FI', 'Finland' UNION ALL
SELECT 'FJ', 'Fiji' UNION ALL
SELECT 'FK', 'Falkland Islands (Malvinas)' UNION ALL
SELECT 'FM', '"Micronesia, Federated States of"' UNION ALL
SELECT 'FO', 'Faroe Islands' UNION ALL
SELECT 'FR', 'France' UNION ALL
SELECT 'GA', 'Gabon' UNION ALL
SELECT 'GB', 'United Kingdom' UNION ALL
SELECT 'GD', 'Grenada' UNION ALL
SELECT 'GE', 'Georgia' UNION ALL
SELECT 'GF', 'French Guiana' UNION ALL
SELECT 'GG', 'Guernsey' UNION ALL
SELECT 'GH', 'Ghana' UNION ALL
SELECT 'GI', 'Gibraltar' UNION ALL
SELECT 'GL', 'Greenland' UNION ALL
SELECT 'GM', 'Gambia' UNION ALL
SELECT 'GN', 'Guinea' UNION ALL
SELECT 'GP', 'Guadeloupe' UNION ALL
SELECT 'GQ', 'Equatorial Guinea' UNION ALL
SELECT 'GR', 'Greece' UNION ALL
SELECT 'GS', 'South Georgia and the South Sandwich Islands' UNION ALL
SELECT 'GT', 'Guatemala' UNION ALL
SELECT 'GU', 'Guam' UNION ALL
SELECT 'GW', 'Guinea-Bissau' UNION ALL
SELECT 'GY', 'Guyana' UNION ALL
SELECT 'HK', 'Hong Kong' UNION ALL
SELECT 'HM', 'Heard Island and McDonald Islands' UNION ALL
SELECT 'HN', 'Honduras' UNION ALL
SELECT 'HR', 'Croatia' UNION ALL
SELECT 'HT', 'Haiti' UNION ALL
SELECT 'HU', 'Hungary' UNION ALL
SELECT 'ID', 'Indonesia' UNION ALL
SELECT 'IE', 'Ireland' UNION ALL
SELECT 'IL', 'Israel' UNION ALL
SELECT 'IM', 'Isle of Man' UNION ALL
SELECT 'IN', 'India' UNION ALL
SELECT 'IO', 'British Indian Ocean Territory' UNION ALL
SELECT 'IQ', 'Iraq' UNION ALL
SELECT 'IR', '"Iran, Islamic Republic of"' UNION ALL
SELECT 'IS', 'Iceland' UNION ALL
SELECT 'IT', 'Italy' UNION ALL
SELECT 'JE', 'Jersey' UNION ALL
SELECT 'JM', 'Jamaica' UNION ALL
SELECT 'JO', 'Jordan' UNION ALL
SELECT 'JP', 'Japan' UNION ALL
SELECT 'KE', 'Kenya' UNION ALL
SELECT 'KG', 'Kyrgyzstan' UNION ALL
SELECT 'KH', 'Cambodia' UNION ALL
SELECT 'KI', 'Kiribati' UNION ALL
SELECT 'KM', 'Comoros' UNION ALL
SELECT 'KN', 'Saint Kitts and Nevis' UNION ALL
SELECT 'KP', '"Korea, Democratic Peoples Republic of"' UNION ALL
SELECT 'KR', '"Korea, Republic of"' UNION ALL
SELECT 'KW', 'Kuwait' UNION ALL
SELECT 'KY', 'Cayman Islands' UNION ALL
SELECT 'KZ', 'Kazakhstan' UNION ALL
SELECT 'LA', 'Lao Peoples Democratic Republic' UNION ALL
SELECT 'LB', 'Lebanon' UNION ALL
SELECT 'LC', 'Saint Lucia' UNION ALL
SELECT 'LI', 'Liechtenstein' UNION ALL
SELECT 'LK', 'Sri Lanka' UNION ALL
SELECT 'LR', 'Liberia' UNION ALL
SELECT 'LS', 'Lesotho' UNION ALL
SELECT 'LT', 'Lithuania' UNION ALL
SELECT 'LU', 'Luxembourg' UNION ALL
SELECT 'LV', 'Latvia' UNION ALL
SELECT 'LY', 'Libyan Arab Jamahiriya' UNION ALL
SELECT 'MA', 'Morocco' UNION ALL
SELECT 'MC', 'Monaco' UNION ALL
SELECT 'MD', '"Moldova, Republic of"' UNION ALL
SELECT 'ME', 'Montenegro' UNION ALL
SELECT 'MF', 'Saint Martin (French part)' UNION ALL
SELECT 'MG', 'Madagascar' UNION ALL
SELECT 'MH', 'Marshall Islands' UNION ALL
SELECT 'MK', '"Macedonia, the former Yugoslav Republic of"' UNION ALL
SELECT 'ML', 'Mali' UNION ALL
SELECT 'MM', 'Myanmar' UNION ALL
SELECT 'MN', 'Mongolia' UNION ALL
SELECT 'MO', 'Macao' UNION ALL
SELECT 'MP', 'Northern Mariana Islands' UNION ALL
SELECT 'MQ', 'Martinique' UNION ALL
SELECT 'MR', 'Mauritania' UNION ALL
SELECT 'MS', 'Montserrat' UNION ALL
SELECT 'MT', 'Malta' UNION ALL
SELECT 'MU', 'Mauritius' UNION ALL
SELECT 'MV', 'Maldives' UNION ALL
SELECT 'MW', 'Malawi' UNION ALL
SELECT 'MX', 'Mexico' UNION ALL
SELECT 'MY', 'Malaysia' UNION ALL
SELECT 'MZ', 'Mozambique' UNION ALL
SELECT 'NA', 'Namibia' UNION ALL
SELECT 'NC', 'New Caledonia' UNION ALL
SELECT 'NE', 'Niger' UNION ALL
SELECT 'NF', 'Norfolk Island' UNION ALL
SELECT 'NG', 'Nigeria' UNION ALL
SELECT 'NI', 'Nicaragua' UNION ALL
SELECT 'NL', 'Netherlands' UNION ALL
SELECT 'NO', 'Norway' UNION ALL
SELECT 'NP', 'Nepal' UNION ALL
SELECT 'NR', 'Nauru' UNION ALL
SELECT 'NU', 'Niue' UNION ALL
SELECT 'NZ', 'New Zealand' UNION ALL
SELECT 'OM', 'Oman' UNION ALL
SELECT 'PA', 'Panama' UNION ALL
SELECT 'PE', 'Peru' UNION ALL
SELECT 'PF', 'French Polynesia' UNION ALL
SELECT 'PG', 'Papua New Guinea' UNION ALL
SELECT 'PH', 'Philippines' UNION ALL
SELECT 'PK', 'Pakistan' UNION ALL
SELECT 'PL', 'Poland' UNION ALL
SELECT 'PM', 'Saint Pierre and Miquelon' UNION ALL
SELECT 'PN', 'Pitcairn' UNION ALL
SELECT 'PR', 'Puerto Rico' UNION ALL
SELECT 'PS', '"Palestinian Territory, Occupied"' UNION ALL
SELECT 'PT', 'Portugal' UNION ALL
SELECT 'PW', 'Palau' UNION ALL
SELECT 'PY', 'Paraguay' UNION ALL
SELECT 'QA', 'Qatar' UNION ALL
SELECT 'RE', 'Reunion !R‚union' UNION ALL
SELECT 'RO', 'Romania' UNION ALL
SELECT 'RS', 'Serbia' UNION ALL
SELECT 'RU', 'Russian Federation' UNION ALL
SELECT 'RW', 'Rwanda' UNION ALL
SELECT 'SA', 'Saudi Arabia' UNION ALL
SELECT 'SB', 'Solomon Islands' UNION ALL
SELECT 'SC', 'Seychelles' UNION ALL
SELECT 'SD', 'Sudan' UNION ALL
SELECT 'SE', 'Sweden' UNION ALL
SELECT 'SG', 'Singapore' UNION ALL
SELECT 'SH', '"Saint Helena, Ascension and Tristan da Cunha"' UNION ALL
SELECT 'SI', 'Slovenia' UNION ALL
SELECT 'SJ', 'Svalbard and Jan Mayen' UNION ALL
SELECT 'SK', 'Slovakia' UNION ALL
SELECT 'SL', 'Sierra Leone' UNION ALL
SELECT 'SM', 'San Marino' UNION ALL
SELECT 'SN', 'Senegal' UNION ALL
SELECT 'SO', 'Somalia' UNION ALL
SELECT 'SR', 'Suriname' UNION ALL
SELECT 'ST', 'Sao Tome and Principe' UNION ALL
SELECT 'SV', 'El Salvador' UNION ALL
SELECT 'SX', 'Sint Maarten (Dutch part)' UNION ALL
SELECT 'SY', 'Syrian Arab Republic' UNION ALL
SELECT 'SZ', 'Swaziland' UNION ALL
SELECT 'TC', 'Turks and Caicos Islands' UNION ALL
SELECT 'TD', 'Chad' UNION ALL
SELECT 'TF', 'French Southern Territories' UNION ALL
SELECT 'TG', 'Togo' UNION ALL
SELECT 'TH', 'Thailand' UNION ALL
SELECT 'TJ', 'Tajikistan' UNION ALL
SELECT 'TK', 'Tokelau' UNION ALL
SELECT 'TL', 'Timor-Leste' UNION ALL
SELECT 'TM', 'Turkmenistan' UNION ALL
SELECT 'TN', 'Tunisia' UNION ALL
SELECT 'TO', 'Tonga' UNION ALL
SELECT 'TR', 'Turkey' UNION ALL
SELECT 'TT', 'Trinidad and Tobago' UNION ALL
SELECT 'TV', 'Tuvalu' UNION ALL
SELECT 'TW', '"Taiwan, Province of China"' UNION ALL
SELECT 'TZ', '"Tanzania, United Republic of"' UNION ALL
SELECT 'UA', 'Ukraine' UNION ALL
SELECT 'UG', 'Uganda' UNION ALL
SELECT 'UM', 'United States Minor Outlying Islands' UNION ALL
SELECT 'US', 'United States' UNION ALL
SELECT 'UY', 'Uruguay' UNION ALL
SELECT 'UZ', 'Uzbekistan' UNION ALL
SELECT 'VA', 'Holy See (Vatican City State)' UNION ALL
SELECT 'VC', 'Saint Vincent and the Grenadines' UNION ALL
SELECT 'VE', '"Venezuela, Bolivarian Republic of"' UNION ALL
SELECT 'VG', '"Virgin Islands, British"' UNION ALL
SELECT 'VI', '"Virgin Islands, U.S."' UNION ALL
SELECT 'VN', 'Viet Nam' UNION ALL
SELECT 'VU', 'Vanuatu' UNION ALL
SELECT 'WF', 'Wallis and Futuna' UNION ALL
SELECT 'WS', 'Samoa' UNION ALL
SELECT 'YE', 'Yemen' UNION ALL
SELECT 'YT', 'Mayotte' UNION ALL
SELECT 'ZA', 'South Africa' UNION ALL
SELECT 'ZM', 'Zambia' UNION ALL
SELECT 'ZW', 'Zimbabwe' 

2 comments:

Chris Ashton said...

Nice one. Looks like this will help me out with my website, and save me a massive amount of time.

Bob Bello said...

Hi, I noticed you have freeccnaworkbook.com on your blogroll but did not have the new study guide site too at www.FreeCCNAStudyGuide.com as it is free and good to so you might want to post that too :)

Post a Comment