rb_name_mapping.sql

CREATE TABLE name_mapping as
SELECT r.id as rb_id, r.name, c.id as code FROM rb_countries r, country_codes c where c.name = r.name;

INSERT INTO name_mapping (rb_id, name) SELECT id as rb_id, name FROM rb_countries where name not in (select name from country_codes);

ALTER TABLE name_mapping ADD PRIMARY KEY (rb_id);

--manual edit

1;"Afghanistan";"AF"
2;"Albania";"AL"
4;"Algeria";"DZ"
5;"American Samoa";"AS"
6;"Andorra";"AD"
7;"Angola";"AO"
8;"Anguilla";"AI"
9;"Antigua & Barbuda";"AG"
10;"Argentina";"AR"
11;"Armenia";"AM"
12;"Aruba";"AW"
13;"Ascension Island";"SH"
14;"Australia";"AU"
15;"Austria";"AT"
16;"Azerbaijan";"AZ"
17;"Bahamas";"BS"
18;"Bahrain";"BH"
19;"Bangladesh";"BD"
20;"Barbados";"BB"
22;"Belarus";"BY"
23;"Belgium";"BE"
24;"Belize";"BZ"
25;"Benin";"BJ"
26;"Bermuda";"BM"
27;"Bhutan";"BT"
28;"Bolivia";"BO"
29;"Bosnia";"BA"
30;"Botswana";"BW"
31;"Brazil";"BR"
32;"British Virgin Islands";"VG"
33;"Brunei";"BN"
34;"Bulgaria";"BG"
35;"Burkina Faso";"BF"
36;"Burundi";"BI"
37;"Cambodia";"KH"
38;"Cameroon";"CM"
39;"Canada";"CA"
40;"Cape Verde Islands";"CV"
41;"Cayman Islands";"KY"
42;"Central African Republic";"CF"
43;"Chad";"TD"
44;"Chile";"CL"
45;"China";"CN"
46;"Christmas Island";"CX"
47;"Cocos-Keeling Islands";"CC"
48;"Colombia";"CO"
49;"Comoros";"KM"
50;"Congo";"CG"
51;"Cook Islands";"CK"
52;"Costa Rica";"CR"
53;"Croatia";"HR"
54;"Cuba";"CU"
55;"Cyprus";"CY"
56;"Czech Republic";"CZ"
57;"Dem Rep of Congo";"CD"
58;"Denmark";"DK"
59;"Diego Garcia";"DG"
60;"Djibouti";"DJ"
61;"Dominica";"DM"
62;"Dominican Republic";"DO"
63;"Ecuador";"EC"
64;"Egypt";"EG"
65;"El Salvador";"SV"
66;"Equatorial Guinea";"GQ"
67;"Estonia";"EE"
68;"Ethiopia";"ET"
69;"Falkland Islands";"FK"
70;"Fiji Islands";"FJ"
71;"Finland";"FI"
72;"France";"FR"
74;"French Guiana";"GF"
75;"French Polynesia";"PF"
76;"Gabon";"GA"
77;"Gambia";"GM"
78;"Georgia";"GE"
79;"Germany";"DE"
80;"Ghana";"GH"
81;"Gibraltar";"GI"
82;"Greece";"GR"
83;"Greenland";"GL"
84;"Grenada";"GD"
85;"Guadeloupe";"GP"
86;"Guam";"GU"
88;"Guatemala";"GT"
89;"Guinea";"GN"
90;"Guyana";"GY"
91;"Haiti";"HT"
92;"Honduras";"HN"
93;"Hong Kong";"HK"
94;"Hungary";"HU"
95;"Iceland";"IS"
96;"India";"IN"
97;"Indonesia";"ID"
98;"Iran";"IR"
99;"Iraq";"IQ"
100;"Ireland";"IE"
101;"Israel";"IL"
102;"Italy";"IT"
103;"Ivory Coast";"CI"
104;"Jamaica";"JM"
105;"Japan";"JP"
106;"Jordan";"JO"
107;"Kazakhstan";"KZ"
108;"Kenya";"KE"
109;"Kiribati Republic";"KI"
110;"North Korea";"KP"
111;"South Korea";"KR"
112;"Kuwait";"KW"
113;"Kyrgyz Republic";"KG"
114;"Laos";"LA"
115;"Latvia";"LV"
116;"Lebanon";"LB"
117;"Lesotho";"LS"
118;"Liberia";"LR"
119;"Libya";"LY"
120;"Liechtenstein";"LI"
121;"Lithuania";"LT"
122;"Luxembourg";"LU"
123;"Macau";"MO"
124;"Madagascar";"MG"
125;"Malawi";"MW"
126;"Malaysia";"MY"
127;"Maldives";"MV"
128;"Mali";"ML"
129;"Malta";"MT"
130;"Marshall Islands";"MH"
131;"Martinique";"MQ"
132;"Mayotte Island";"YT"
133;"Mexico";"MX"
134;"Moldova";"MD"
135;"Monaco";"MC"
136;"Mongolia";"MN"
137;"Monserrat";"MS"
138;"Morocco";"MA"
139;"Mozambique";"MZ"
140;"Myanmar";"MM"
141;"Namibia";"NA"
142;"Nauru";"NR"
143;"Nepal";"NP"
144;"Netherlands";"NL"
145;"Netherlands Antilles";""
147;"New Caledonia";"NC"
148;"New Zealand";"NZ"
149;"Nicaragua";"NI"
150;"Niger";"NE"
151;"Nigeria";"NG"
152;"Niue";"NU"
153;"Norfolk Island";"NF"
154;"Norway";"NO"
155;"Oman";"OM"
156;"Pakistan";"PK"
157;"Palau";"PW"
158;"Panama";"PA"
159;"Papua New Guinea";"PG"
160;"Paraguay";"PY"
161;"Peru";"PE"
162;"Philippines";"PH"
163;"Poland";"PL"
164;"Portugal";"PT"
165;"Puerto Rico";"PR"
166;"Qatar";"QA"
167;"Romania";"RO"
169;"Russia";"RU"
170;"Rwanda";"RW"
171;"St Lucia";"LC"
172;"Saipan Island";""
173;"San Marino";"SM"
174;"Saudi Arabia";"SA"
175;"Senegal Republic";"SN"
176;"Sierra Leone";"SL"
177;"Singapore";"SG"
178;"Slovak Republic";"SK"
179;"Slovenia";"SI"
180;"Solomon Islands";"SB"
181;"Somalia";"SO"
182;"South Africa";"ZA"
183;"Spain";"ES"
184;"Sri Lanka";"LK"
185;"St Helena";"SH"
186;"St Kitts";"KN"
187;"Sudan";"SD"
188;"Suriname";"SR"
189;"Swaziland";"SZ"
190;"Sweden";"SE"
191;"Switzerland";"CH"
192;"Syria";"SY"
193;"Taiwan";"TW"
194;"Tajikistan";"TJ"
195;"Tanzania";"TZ"
196;"Thailand";"TH"
197;"Tinian Island";"MP"
198;"Togo";"TG"
199;"Tokelau";"TK"
200;"Tonga";"TO"
201;"Trinidad & Tobago";"TT"
202;"Tunisia";"TN"
203;"Turkey";"TR"
204;"Turkmenistan";"TM"
205;"Turks and Caicos Islands";"TC"
206;"Tuvalu";"TV"
207;"Uganda";"UG"
208;"Ukraine";"UA"
209;"United Arab Emirates";"AE"
211;"United States Virgin Islands";"VI"
212;"Uruguay";"UY"
213;"United States";"US"
214;"Uzbekistan";"UZ"
215;"Vanuatu";"VU"
216;"Vatican City";"VA"
217;"Venezuela";"VE"
218;"Vietnam";"VN"
219;"Samoa";"WS"
221;"Serbia";"RS"
222;"Zambia";"ZM"
223;"Zimbabwe";"ZW"
224;"Isle of Man";"IM"
225;"Guernsey";"GG"
226;"Jersey";"JE"
227;"St Vincent & The Grenadines";"VC"
229;"Macedonia";"MK"
230;"Mauritius";"MU"
233;"Ceuta";"EA"
234;"Montenegro";"ME"
235;"Palestine";"PS"
236;"East Timor";""
237;"Tibet";""
238;"Northern Ireland";"GB"
239;"Wales";"GB"
240;"England";"GB"
241;"Scotland";"GB"
242;"Kosovo";"XK"
243;"Eritrea";"ER"
244;"Faroe Islands";"FO"
245;"Seychelles";"SC"
246;"Réunion";"RE"
247;"Micronesia";"FM"
248;"Yemen";"YE"
253;"Guinea-Bissau";"GW"
254;"São Tomé & Principe";"ST"
255;"Northern Marianas";"MP"
257;"Curaçao";"CW"
258;"Sint Maarten";"SX"
259;"South Sudan";"SS"
260;"Turkish Republic of Cyprus";""
261;"Transdniestra";""
262;"Somaliland";"SO"
263;"Abkhazia";""
264;"Nagorno-Karabakh";"AZ"
265;"South Ossetia";""