The Site Doctor Blog

Footprints in the snow of a warped mind

< Back to Blog

Add every country to uCommerce

Something I learnt the other day was that uCommerce uses the culture code you specify for a country to determine the two/three digit ISO code when sending the order to the payment gateway.

This is fine if you know all the various countries -but I didn't so here's a SQL script that will create them all for you. It assumes that you have the default set of countries that are installed with uCommerce and haven't add any yourself. If you have, you may need to alter a few more of the INSERT statements to be UPDATE.

I've had to make some assumptions on which culture code is the main one e.g az-Latn-AZ but have gone for Latin each time. I suspect that this list is also missing a few -if you spot some let me know.

Remember; use at your own risk!

-- Remove any existing country/shipping joins
DELETE FROM uCommerce_ShippingMethodCountry

-- Insert the new countries (and update those that already exist)

SET IDENTITY_INSERT dbo.uCommerce_Country ON

INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (1, 'Afghanistan','ps-AF' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (2, 'Albania','sq-AL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (3, 'Algeria','ar-DZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (4, 'Argentina','es-AR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (5, 'Armenia','hy-AM' ,'0') 
UPDATE uCommerce_Country SET Name = 'Australia', Culture = 'en-AU' , Deleted='0' WHERE CountryId = 6 
UPDATE uCommerce_Country SET Name = 'Austria', Culture = 'de-AT' , Deleted='0' WHERE CountryId = 7 
UPDATE uCommerce_Country SET Name = 'Azerbaijan', Culture = 'az-Cyrl-AZ' , Deleted='0' WHERE CountryId = 8 
UPDATE uCommerce_Country SET Name = 'Bahrain', Culture = 'ar-BH' , Deleted='0' WHERE CountryId = 9 
UPDATE uCommerce_Country SET Name = 'Bangladesh', Culture = 'bn-BD' , Deleted='0' WHERE CountryId = 10 
UPDATE uCommerce_Country SET Name = 'Belarus', Culture = 'be-BY' , Deleted='0' WHERE CountryId = 11 
UPDATE uCommerce_Country SET Name = 'Belgium', Culture = 'nl-BE' , Deleted='0' WHERE CountryId = 12 
UPDATE uCommerce_Country SET Name = 'Belize', Culture = 'en-BZ' , Deleted='0' WHERE CountryId = 13 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (14, 'Bolivarian Republic of Venezuela','es-VE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (15, 'Bolivia','es-BO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (16, 'Bosnia and Herzegovina','hr-BA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (17, 'Botswana','tn-BW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (18, 'Brazil','pt-BR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (19, 'Brunei Darussalam','ms-BN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (20, 'Bulgaria','bg-BG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (21, 'Cambodia','km-KH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (22, 'Canada','en-CA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (23, 'Caribbean','en-029' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (24, 'Chile','es-CL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (25, 'China','zh-CN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (26, 'Colombia','es-CO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (27, 'Costa Rica','es-CR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (28, 'Croatia','hr-HR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (29, 'Czech Republic','cs-CZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (30, 'Denmark','da-DK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (31, 'Dominican Republic','es-DO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (32, 'Ecuador','es-EC' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (33, 'Egypt','ar-EG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (34, 'El Salvador','es-SV' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (35, 'Eritrea','ti-ER' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (36, 'Estonia','et-EE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (37, 'Ethiopia','ti-ET' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (38, 'Faroe Islands','fo-FO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (39, 'Finland','fi-FI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (40, 'France','fr-FR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (41, 'Georgia','ka-GE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (42, 'Germany','de-DE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (43, 'Greece','el-GR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (44, 'Greenland','kl-GL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (45, 'Guatemala','es-GT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (46, 'Honduras','es-HN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (47, 'Hong Kong SAR','zh-HK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (48, 'Hungary','hu-HU' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (49, 'Iceland','is-IS' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (50, 'India','hi-IN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (51, 'Indonesia','id-ID' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (52, 'Iran','fa-IR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (53, 'Iraq','ar-IQ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (54, 'Ireland','en-IE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (55, 'Israel','he-IL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (56, 'Italy','it-IT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (57, 'Jamaica','en-JM' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (58, 'Japan','ja-JP' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (59, 'Jordan','ar-JO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (60, 'Kazakhstan','kk-KZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (61, 'Kenya','sw-KE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (62, 'Korea','ko-KR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (63, 'Kuwait','ar-KW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (64, 'Kyrgyzstan','ky-KG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (65, 'Lao PDR','lo-LA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (66, 'Latvia','lv-LV' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (67, 'Lebanon','ar-LB' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (68, 'Libya','ar-LY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (69, 'Liechtenstein','de-LI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (70, 'Lithuania','lt-LT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (71, 'Luxembourg','lb-LU' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (72, 'Macao SAR','zh-MO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (73, 'Macedonia (Former Yugoslav Republic of Macedonia)','mk-MK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (74, 'Malaysia','ms-MY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (75, 'Maldives','dv-MV' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (76, 'Malta','mt-MT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (77, 'Mexico','es-MX' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (78, 'Mongolia','mn-MN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (79, 'Montenegro','sr-Latn-ME' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (80, 'Morocco','ar-MA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (81, 'Nepal','ne-NP' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (82, 'Netherlands','nl-NL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (83, 'New Zealand','en-NZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (84, 'Nicaragua','es-NI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (85, 'Nigeria','ig-NG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (86, 'Norway','nb-NO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (87, 'Oman','ar-OM' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (88, 'Pakistan','ur-PK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (89, 'Panama','es-PA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (90, 'Paraguay','es-PY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (91, 'Peru','es-PE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (92, 'Philippines','fil-PH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (93, 'Poland','pl-PL' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (94, 'Portugal','pt-PT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (95, 'Principality of Monaco','fr-MC' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (96, 'Puerto Rico','es-PR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (97, 'Qatar','ar-QA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (98, 'Romania','ro-RO' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (99, 'Russia','ru-RU' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (100, 'Rwanda','rw-RW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (101, 'Saudi Arabia','ar-SA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (102, 'Senegal','wo-SN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (103, 'Serbia','sr-Latn-RS' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (104, 'Serbia and Montenegro (Former)','sr-Latn-CS' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (105, 'Singapore','zh-SG' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (106, 'Slovakia','sk-SK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (107, 'Slovenia','sl-SI' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (108, 'South Africa','tn-ZA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (109, 'Spain','es-ES' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (110, 'Sri Lanka','si-LK' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (111, 'Sweden','se-SE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (112, 'Switzerland','fr-CH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (113, 'Syria','syr-SY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (114, 'Taiwan','zh-TW' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (115, 'Tajikistan','tg-Cyrl-TJ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (116, 'Thailand','th-TH' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (117, 'Trinidad and Tobago','en-TT' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (118, 'Tunisia','ar-TN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (119, 'Turkey','tr-TR' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (120, 'Turkmenistan','tk-TM' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (121, 'U.A.E.','ar-AE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (122, 'Ukraine','uk-UA' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (123, 'United Kingdom','en-GB' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (124, 'United States','en-US' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (125, 'Uruguay','es-UY' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (126, 'Uzbekistan','uz-Latn-UZ' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (127, 'Vietnam','vi-VN' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (128, 'Yemen','ar-YE' ,'0') 
INSERT uCommerce_Country (CountryId, Name, Culture , Deleted) VALUES (129, 'Zimbabwe','en-ZW' ,'0') 

SET IDENTITY_INSERT dbo.uCommerce_Country OFF

Enable every country for every shipping method

If you want to enable all shipping methods for all countries you can use this script:

INSERT INTO uCommerce_ShippingMethodCountry 
SELECT csm.ShippingMethodId, cc.CountryId 
FROM uCommerce_Country cc, uCommerce_ShippingMethod csm

Enable every country for every payment method

Finally, if you want to enable all payment methods for all countries you can use this script:

INSERT INTO uCommerce_PaymentMethodCountry
SELECT pm.PaymentMethodId, cc.CountryId 
FROM uCommerce_Country cc, uCommerce_PaymentMethod AS pm

Hope it helps you

Author: Tim on

Liked this post? Got a suggestion? Leave a comment