Country Code

The Script to create the countries and code 

10/3/2014

ALTER TABLE [GeoCity]
ADD [CountryName] NVARCHAR(128) NULL
GO

DELETE
FROM 
  [GeoCity]
WHERE
  [Region] IN ('')
GO

SELECT 'Afghanistan' [CountryName],'Kabul' [Capital],'af' [Code],'93' [Phone]
into #countries
UNION ALL SELECT 'Aland Islands', '', 'ax', ''
UNION ALL SELECT 'Curacao', '', 'cw', ''
UNION ALL SELECT 'Sint Maarten', '', 'sx', ''
UNION ALL SELECT 'Saint Martin', '', 'mf', ''
UNION ALL SELECT 'Saint Barthelemy', '', 'bl', ''
UNION ALL SELECT 'Sint Eustatius', '', 'BQ', ''
UNION ALL SELECT 'Timor Leste', '', 'tl', ''
UNION ALL SELECT 'Palenstinian Territory', '', 'ps', ''
UNION ALL SELECT 'Albania','Tirana','al','355'
UNION ALL SELECT 'Algeria','Algiers','dz','213'
UNION ALL SELECT 'American Samoa','Pago Pago','as','684'
UNION ALL SELECT 'Andorra','Andorra','ad','376'
UNION ALL SELECT 'Angola','Luanda','ao','244'
UNION ALL SELECT 'Anguilla','The Valley','ai','1-264'
UNION ALL SELECT 'Antarctica','None','aq','672'
UNION ALL SELECT 'Antigua and Barbuda','St. Johns','ag','1-268'
UNION ALL SELECT 'Argentina','Buenos Aires','ar','54'
UNION ALL SELECT 'Armenia','Yerevan','am','374'
UNION ALL SELECT 'Aruba','Oranjestad','aw','297'
UNION ALL SELECT 'Australia','Canberra','au','61'
UNION ALL SELECT 'Austria','Vienna','at','43'
UNION ALL SELECT 'Azerbaijan','Baku','az','994'
UNION ALL SELECT 'Bahamas','Nassau','bs','1-242'
UNION ALL SELECT 'Bahrain','Al-Manamah','bh','973'
UNION ALL SELECT 'Bangladesh','Dhaka','bd','880'
UNION ALL SELECT 'Barbados','Bridgetown','bb','1-246'
UNION ALL SELECT 'Belarus','Minsk','by','375'
UNION ALL SELECT 'Belgium','Brussels','be','32'
UNION ALL SELECT 'Belize','Belmopan','bz','501'
UNION ALL SELECT 'Benin','Porto-Novo','bj','229'
UNION ALL SELECT 'Bermuda','Hamilton','bm','1-441'
UNION ALL SELECT 'Bhutan','Thimphu','bt','975'
UNION ALL SELECT 'Bolivia','La Paz','bo','591'
UNION ALL SELECT 'Bosnia-Herzegovina','Sarajevo','ba','387'
UNION ALL SELECT 'Botswana','Gaborone','bw','267'
UNION ALL SELECT 'Bouvet Island','None','bv',''
UNION ALL SELECT 'Brazil','Brasilia','br','55'
UNION ALL SELECT 'British Indian Ocean Territory','None','io',''
UNION ALL SELECT 'Brunei Darussalam','Bandar Seri Begawan','bn','673'
UNION ALL SELECT 'Bulgaria','Sofia','bg','359'
UNION ALL SELECT 'Burkina Faso','Ouagadougou','bf','226'
UNION ALL SELECT 'Burundi','Bujumbura','bi','257'
UNION ALL SELECT 'Cambodia','Phnom Penh','kh','855'
UNION ALL SELECT 'Cameroon','Yaounde','cm','237'
UNION ALL SELECT 'Canada','Ottawa','ca','1'
UNION ALL SELECT 'Cape Verde','Praia','cv','238'
UNION ALL SELECT 'Cayman Islands','Georgetown','ky','1-345'
UNION ALL SELECT 'Central African Republic','Bangui','cf','236'
UNION ALL SELECT 'Chad','N''Djamena','td','235'
UNION ALL SELECT 'Chile','Santiago','cl','56'
UNION ALL SELECT 'China','Beijing','cn','86'
UNION ALL SELECT 'Christmas Island','The Settlement','cx','61'
UNION ALL SELECT 'Cocos (Keeling) Islands','West Island','cc','61'
UNION ALL SELECT 'Colombia','Bogota','co','57'
UNION ALL SELECT 'Comoros','Moroni','km','269'
UNION ALL SELECT 'Congo','Brazzaville','cg','242'
UNION ALL SELECT 'Congo, Dem. Republic','Kinshasa','cd','243'
UNION ALL SELECT 'Cook Islands','Avarua','ck','682'
UNION ALL SELECT 'Costa Rica','San Jose','cr','506'
UNION ALL SELECT 'Croatia','Zagreb','hr','385'
UNION ALL SELECT 'Cuba','Havana','cu','53'
UNION ALL SELECT 'Cyprus','Nicosia','cy','357'
UNION ALL SELECT 'Czech Rep.','Prague','cz','420'
UNION ALL SELECT 'Denmark','Copenhagen','dk','45'
UNION ALL SELECT 'Djibouti','Djibouti','dj','253'
UNION ALL SELECT 'Dominica','Roseau','dm','1-767'
UNION ALL SELECT 'Dominican Republic','Santo Domingo','do','809'
UNION ALL SELECT 'Ecuador','Quito','ec','593'
UNION ALL SELECT 'Egypt','Cairo','eg','20'
UNION ALL SELECT 'El Salvador','San Salvador','sv','503'
UNION ALL SELECT 'Equatorial Guinea','Malabo','gq','240'
UNION ALL SELECT 'Eritrea','Asmara','er','291'
UNION ALL SELECT 'Estonia','Tallinn','ee','372'
UNION ALL SELECT 'Ethiopia','Addis Ababa','et','251'
UNION ALL SELECT 'European Union','Brussels','eu.int',''
UNION ALL SELECT 'Falkland Islands (Malvinas)','Stanley','fk','500'
UNION ALL SELECT 'Faroe Islands','Torshavn','fo','298'
UNION ALL SELECT 'Fiji','Suva','fj','679'
UNION ALL SELECT 'Finland','Helsinki','fi','358'
UNION ALL SELECT 'France','Paris','fr','33'
UNION ALL SELECT 'French Guiana','Cayenne','gf','594'
UNION ALL SELECT 'French Southern Territories','None','tf',''
UNION ALL SELECT 'Gabon','Libreville','ga','241'
UNION ALL SELECT 'Gambia','Banjul','gm','220'
UNION ALL SELECT 'Georgia','Tbilisi','ge','995'
UNION ALL SELECT 'Germany','Berlin','de','49'
UNION ALL SELECT 'Ghana','Accra','gh','233'
UNION ALL SELECT 'Gibraltar','Gibraltar','gi','350'
UNION ALL SELECT 'Great Britain','London','gb','44'
UNION ALL SELECT 'Greece','Athens','gr','30'
UNION ALL SELECT 'Greenland','Godthab','gl','299'
UNION ALL SELECT 'Grenada','St. George''s','gd','1-473'
UNION ALL SELECT 'Guadeloupe (French)','Basse-Terre','gp','590'
UNION ALL SELECT 'Guam (USA)','Agana','gu','1-671'
UNION ALL SELECT 'Guatemala','Guatemala City','gt','502'
UNION ALL SELECT 'Guernsey','St. Peter Port','gg',''
UNION ALL SELECT 'Guinea','Conakry','gn','224'
UNION ALL SELECT 'Guinea Bissau','Bissau','gw','245'
UNION ALL SELECT 'Guyana','Georgetown','gy','592'
UNION ALL SELECT 'Haiti','Port-au-Prince','ht','509'
UNION ALL SELECT 'Heard Island and McDonald Islands','None','hm',''
UNION ALL SELECT 'Honduras','Tegucigalpa','hn','504'
UNION ALL SELECT 'Hong Kong','Victoria','hk','852'
UNION ALL SELECT 'Hungary','Budapest','hu','36'
UNION ALL SELECT 'Iceland','Reykjavik','is','354'
UNION ALL SELECT 'India','New Delhi','in','91'
UNION ALL SELECT 'Indonesia','Jakarta','id','62'
UNION ALL SELECT 'Iran','Tehran','ir','98'
UNION ALL SELECT 'Iraq','Baghdad','iq','964'
UNION ALL SELECT 'Ireland','Dublin','ie','353'
UNION ALL SELECT 'Isle of Man','Douglas','im',''
UNION ALL SELECT 'Israel','Jerusalem','il','972'
UNION ALL SELECT 'Italy','Rome','it','39'
UNION ALL SELECT 'Ivory Coast','Abidjan','ci','225'
UNION ALL SELECT 'Jamaica','Kingston','jm','1-876'
UNION ALL SELECT 'Japan','Tokyo','jp','81'
UNION ALL SELECT 'Jersey','Saint Helier','je',''
UNION ALL SELECT 'Jordan','Amman','jo','962'
UNION ALL SELECT 'Kazakhstan','Astana','kz','7'
UNION ALL SELECT 'Kenya','Nairobi','ke','254'
UNION ALL SELECT 'Kiribati','Tarawa','ki','686'
UNION ALL SELECT 'Korea-North','Pyongyang','kp','850'
UNION ALL SELECT 'Korea-South','Seoul','kr','82'
UNION ALL SELECT 'Kuwait','Kuwait City','kw','965'
UNION ALL SELECT 'Kyrgyzstan','Bishkek','kg','996'
UNION ALL SELECT 'Laos','Vientiane','la','856'
UNION ALL SELECT 'Latvia','Riga','lv','371'
UNION ALL SELECT 'Lebanon','Beirut','lb','961'
UNION ALL SELECT 'Lesotho','Maseru','ls','266'
UNION ALL SELECT 'Liberia','Monrovia','lr','231'
UNION ALL SELECT 'Libya','Tripoli','ly','218'
UNION ALL SELECT 'Liechtenstein','Vaduz','li','423'
UNION ALL SELECT 'Lithuania','Vilnius','lt','370'
UNION ALL SELECT 'Luxembourg','Luxembourg','lu','352'
UNION ALL SELECT 'Macau','Macau','mo','853'
UNION ALL SELECT 'Macedonia','Skopje','mk','389'
UNION ALL SELECT 'Madagascar','Antananarivo','mg','261'
UNION ALL SELECT 'Malawi','Lilongwe','mw','265'
UNION ALL SELECT 'Malaysia','Kuala Lumpur','my','60'
UNION ALL SELECT 'Maldives','Male','mv','960'
UNION ALL SELECT 'Mali','Bamako','ml','223'
UNION ALL SELECT 'Malta','Valletta','mt','356'
UNION ALL SELECT 'Marshall Islands','Majuro','mh','692'
UNION ALL SELECT 'Martinique (French)','Fort-de-France','mq','596'
UNION ALL SELECT 'Mauritania','Nouakchott','mr','222'
UNION ALL SELECT 'Mauritius','Port Louis','mu','230'
UNION ALL SELECT 'Mayotte','Dzaoudzi','yt','269'
UNION ALL SELECT 'Mexico','Mexico City','mx','52'
UNION ALL SELECT 'Micronesia','Palikir','fm','691'
UNION ALL SELECT 'Moldova','Kishinev','md','373'
UNION ALL SELECT 'Monaco','Monaco','mc','377'
UNION ALL SELECT 'Mongolia','Ulan Bator','mn','976'
UNION ALL SELECT 'Montenegro','Podgorica','me','382'
UNION ALL SELECT 'Montserrat','Plymouth','ms','1-664'
UNION ALL SELECT 'Morocco','Rabat','ma','212'
UNION ALL SELECT 'Mozambique','Maputo','mz','258'
UNION ALL SELECT 'Myanmar','Naypyidaw','mm','95'
UNION ALL SELECT 'Namibia','Windhoek','na','264'
UNION ALL SELECT 'Nauru','Yaren','nr','674'
UNION ALL SELECT 'Nepal','Kathmandu','np','977'
UNION ALL SELECT 'Netherlands','Amsterdam','nl','31'
UNION ALL SELECT 'Netherlands Antilles','Willemstad','an','599'
UNION ALL SELECT 'New Caledonia (French)','Noumea','nc','687'
UNION ALL SELECT 'New Zealand','Wellington','nz','64'
UNION ALL SELECT 'Nicaragua','Managua','ni','505'
UNION ALL SELECT 'Niger','Niamey','ne','227'
UNION ALL SELECT 'Nigeria','Lagos','ng','234'
UNION ALL SELECT 'Niue','Alofi','nu','683'
UNION ALL SELECT 'Norfolk Island','Kingston','nf','672'
UNION ALL SELECT 'Northern Mariana Islands','Saipan','mp','670'
UNION ALL SELECT 'Norway','Oslo','no','47'
UNION ALL SELECT 'Oman','Muscat','om','968'
UNION ALL SELECT 'Pakistan','Islamabad','pk','92'
UNION ALL SELECT 'Palau','Koror','pw','680'
UNION ALL SELECT 'Panama','Panama City','pa','507'
UNION ALL SELECT 'Papua New Guinea','Port Moresby','pg','675'
UNION ALL SELECT 'Paraguay','Asuncion','py','595'
UNION ALL SELECT 'Peru','Lima','pe','51'
UNION ALL SELECT 'Philippines','Manila','ph','63'
UNION ALL SELECT 'Pitcairn Island','Adamstown','pn',''
UNION ALL SELECT 'Poland','Warsaw','pl','48'
UNION ALL SELECT 'Polynesia (French)','Papeete','pf','689'
UNION ALL SELECT 'Portugal','Lisbon','pt','351'
UNION ALL SELECT 'Puerto Rico','San Juan','pr','1-787'
UNION ALL SELECT 'Qatar','Doha','qa','974'
UNION ALL SELECT 'Reunion (French)','Saint-Denis','re','262'
UNION ALL SELECT 'Romania','Bucharest','ro','40'
UNION ALL SELECT 'Russia','Moscow','ru','7'
UNION ALL SELECT 'Rwanda','Kigali','rw','250'
UNION ALL SELECT 'Saint Helena','Jamestown','sh','290'
UNION ALL SELECT 'Saint Kitts & Nevis Anguilla','Basseterre','kn','1-869'
UNION ALL SELECT 'Saint Lucia','Castries','lc','1-758'
UNION ALL SELECT 'Saint Pierre and Miquelon','St. Pierre','pm','508'
UNION ALL SELECT 'Saint Vincent & Grenadines','Kingstown','vc','1-784'
UNION ALL SELECT 'Samoa','Apia','ws','684'
UNION ALL SELECT 'San Marino','San Marino','sm','378'
UNION ALL SELECT 'Sao Tome and Principe','Sao Tome','st','239'
UNION ALL SELECT 'Saudi Arabia','Riyadh','sa','966'
UNION ALL SELECT 'Senegal','Dakar','sn','221'
UNION ALL SELECT 'Serbia','Belgrade','rs','381'
UNION ALL SELECT 'Seychelles','Victoria','sc','248'
UNION ALL SELECT 'Sierra Leone','Freetown','sl','232'
UNION ALL SELECT 'Singapore','Singapore','sg','65'
UNION ALL SELECT 'Slovakia','Bratislava','sk','421'
UNION ALL SELECT 'Slovenia','Ljubljana','si','386'
UNION ALL SELECT 'Solomon Islands','Honiara','sb','677'
UNION ALL SELECT 'Somalia','Mogadishu','so','252'
UNION ALL SELECT 'South Africa','Pretoria','za','27'
UNION ALL SELECT 'South Georgia & South Sandwich Islands','None','gs',''
UNION ALL SELECT 'South Sudan','Ramciel','ss',''
UNION ALL SELECT 'Spain','Madrid','es','34'
UNION ALL SELECT 'Sri Lanka','Colombo','lk','94'
UNION ALL SELECT 'Sudan','Khartoum','sd','249'
UNION ALL SELECT 'Suriname','Paramaribo','sr','597'
UNION ALL SELECT 'Svalbard and Jan Mayen Islands','Longyearbyen','sj',''
UNION ALL SELECT 'Swaziland','Mbabane','sz','268'
UNION ALL SELECT 'Sweden','Stockholm','se','46'
UNION ALL SELECT 'Switzerland','Bern','ch','41'
UNION ALL SELECT 'Syria','Damascus','sy','963'
UNION ALL SELECT 'Taiwan','Taipei','tw','886'
UNION ALL SELECT 'Tajikistan','Dushanbe','tj','992'
UNION ALL SELECT 'Tanzania','Dodoma','tz','255'
UNION ALL SELECT 'Thailand','Bangkok','th','66'
UNION ALL SELECT 'Togo','Lome','tg','228'
UNION ALL SELECT 'Tokelau','None','tk','690'
UNION ALL SELECT 'Tonga','Nuku''alofa','to','676'
UNION ALL SELECT 'Trinidad and Tobago','Port of Spain','tt','1-868'
UNION ALL SELECT 'Tunisia','Tunis','tn','216'
UNION ALL SELECT 'Turkey','Ankara','tr','90'
UNION ALL SELECT 'Turkmenistan','Ashgabat','tm','993'
UNION ALL SELECT 'Turks and Caicos Islands','Grand Turk','tc','1-649'
UNION ALL SELECT 'Tuvalu','Funafuti','tv','688'
UNION ALL SELECT 'U.K.','London','uk','44'
UNION ALL SELECT 'Uganda','Kampala','ug','256'
UNION ALL SELECT 'Ukraine','Kiev','ua','380'
UNION ALL SELECT 'United Arab Emirates','Abu Dhabi','ae','971'
UNION ALL SELECT 'Uruguay','Montevideo','uy','598'
UNION ALL SELECT 'USA','Washington','us','1'
UNION ALL SELECT 'USA Minor Outlying Islands','None','um',''
UNION ALL SELECT 'Uzbekistan','Tashkent','uz','998'
UNION ALL SELECT 'Vanuatu','Port Vila','vu','678'
UNION ALL SELECT 'Vatican','Vatican City','va','39'
UNION ALL SELECT 'Venezuela','Caracas','ve','58'
UNION ALL SELECT 'Vietnam','Hanoi','vn','84'
UNION ALL SELECT 'Virgin Islands (British)','Road Town','vg','1-284'
UNION ALL SELECT 'Virgin Islands (USA)','Charlotte Amalie','vi','1-340'
UNION ALL SELECT 'Wallis and Futuna Islands','Mata-Utu','wf','681'
UNION ALL SELECT 'Western Sahara','El Aaiun','eh',''
UNION ALL SELECT 'Yemen','San''a','ye','967'
UNION ALL SELECT 'Zambia','Lusaka','zm','260'
UNION ALL SELECT 'Zimbabwe','Harare','zw','263';

UPDATE 
  [GeoCity]
SET 
  [GeoCity].[CountryName] = [Countries].[CountryName]
FROM
  [GeoCity]
INNER JOIN 
  #countries [Countries] ON [GeoCity].[Country] = [Countries].[Code]
WHERE
  [GeoCity].[CountryName] IS NULL;

DROP TABLE #countries;
GO