Thread: Adding Countries in bulk

Does anyone have a sql query or know of a way to bulk import the countries and states into the database. (http://manual.aspdotnetstorefront.co...countries.aspx)

We removed all of the countries except USA and CA, but now we're planning to do International sales so we need to add them back...

Thanks!

If you open the create DB script located under the DB folder, you should be able to see it there. Just copy/paste that portion and run it against your DB.

e.g.

Code:

INSERT [dbo].Country(Name,TwoLetterISOCode,ThreeLetterISOCode,NumericISOCode,DisplayOrder) values('Argentina','AR','ARG','032',2);

Yeah, that should work perfect when we want to add them in...

Good thinkin'

I had to do the same. Here's the complete code for all countries

Code:

INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(80,'Other - United Kingdom','GBR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(1,'Other - United States','USA',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(2,'Other - Canada','CAN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(3,'Other - Argentina','ARG',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(4,'Other - Armenia','ARM',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(5,'Other - Aruba','ABW',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(6,'Other - Australia','AUS',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(7,'Other - Austria','AUT',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(8,'Other - Azerbaijan','AZE',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(9,'Other - Bahamas','BHS',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(10,'Other - Bangladesh','BGD',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(11,'Other - Belarus','BLR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(12,'Other - Belgium','BEL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(13,'Other - Belize','BLZ',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(14,'Other - Bermuda','BMU',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(15,'Other - Bolivia','BOL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(16,'Other - Bosnia and Herzegovina','BIH',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(17,'Other - Brazil','BRA',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(18,'Other - Bulgaria','BGR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(19,'Other - Cayman Islands','CYM',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(20,'Other - Chile','CHL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(21,'Other - China','CHN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(22,'Other - Colombia','COL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(23,'Other - Costa Rica','CRI',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(24,'Other - Croatia','HRV',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(25,'Other - Cuba','CUB',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(26,'Other - Cyprus','CYP',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(27,'Other - Czech Republic','CZE',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(28,'Other - Denmark','DNK',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(29,'Other - Dominican Republic','DOM',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(30,'Other - Ecuador','ECU',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(31,'Other - Egypt','EGY',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(32,'Other - Finland','FIN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(33,'Other - France','FRA',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(34,'Other - Georgia','GEO',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(35,'Other - Germany','DEU',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(36,'Other - Gibraltar','GIB',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(37,'Other - Greece','GRC',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(38,'Other - Guatemala','GTM',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(39,'Other - Hong Kong','HKG',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(40,'Other - Hungary','HUN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(41,'Other - India','IND',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(42,'Other - Indonesia','IDN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(43,'Other - Ireland','IRL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(44,'Other - Israel','ISR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(45,'Other - Italy','ITA',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(46,'Other - Jamaica','JAM',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(47,'Other - Japan','JPN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(48,'Other - Jordan','JOR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(49,'Other - Kazakhstan','KAZ',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(50,'Other - Korea','PRK',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(51,'Other - Kuwait','KWT',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(52,'Other - Malaysia','MYS',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(53,'Other - Mexico','MEX',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(54,'Other - Netherlands','NLD',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(55,'Other - New Zealand','NZL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(56,'Other - Norway','NOR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(57,'Other - Pakistan','PAK',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(58,'Other - Paraguay','PRY',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(59,'Other - Peru','PER',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(60,'Other - Philippines','PHL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(61,'Other - Poland','POL',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(62,'Other - Portugal','PRT',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(63,'Other - Puerto Rico','PRI',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(64,'Other - Qatar','QAT',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(65,'Other - Romania','ROM',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(66,'Other - Russian Federation','RUS',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(67,'Other - Saudi Arabia','SAU',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(68,'Other - Singapore','SGP',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(69,'Other - Slovakia','SVK',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(70,'Other - Slovenia','SVN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(71,'Other - South Africa','ZAF',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(72,'Other - Spain','ESP',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(73,'Other - Sweden','SWE',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(74,'Other - Switzerland','CHE',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(75,'Other - Taiwan, Province of China','TWN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(76,'Other - Thailand','THA',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(77,'Other - Turkey','TUR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(78,'Other - Ukraine','UKR',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(79,'Other - United Arab Emirates','ARE',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(81,'Other - United States minor outlying islands','UMI',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(82,'Other - Uruguay','URY',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(83,'Other - Uzbekistan','UZB',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(84,'Other - Venezuela','VEN',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(85,'Other - Serbia, Republic of','SRB',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(86,'Other - Monaco','MCO',1);
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(87,'Other - Afghanistan','AFG',1);

Hi
I would like insert Japanese states/country in Japanese characters as below. script executed and inserted without error. But all characters are became '???' when loaded from DB, and I had to edit them one-by-one from inline editing form and Update the record so they can be correct. Can someone help?

---------------------------------------
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(47,'福島県','福島県',1)
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(47,'岩手県','岩手県',1)
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(47,'秋田県','秋田県',1)
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(47,'山形県','山形県',1)
INSERT [dbo].State(CountryID,Name,Abbreviation,DisplayOrder) values(47,'宮城県','宮城県',1)
----------------------------------------

thanks

Did you notice. You have no Kenya in the countries. Countires is the easy bit. Now do the states for all!