I took the information from here: http://www.statoids.com/uth.html, and google translated names into Thai. I have doubled check the names with my Thai friends and they are correct. Thanks to Gwillim Law to allow me to post this content!
This simple code inserts the Provinces and Districts as "States".
declare @provinces xml
set @provinces=N'
<provinces>
<Province NameUs="Amnat Charoen" nameth="อำนาจเจริญ" HASC="TH.AC" ISO="37" FIPS="TH77" REG="NE" />
<Province NameUs="Ang Thong" nameth="อ่างทอง" HASC="TH.AT" ISO="15" FIPS="TH35" REG="C" />
<Province NameUs="Bangkok Metropolis" nameth="กรุงเทพมหานคร" HASC="TH.BM" ISO="10" FIPS="TH40" REG="C" />
<Province NameUs="Bueng Kan" nameth="บึงกาญจน์" HASC="TH.BK" ISO="" FIPS="TH81" REG="NE" />
<Province NameUs="Buri Ram" nameth="บุรีรัมย์" HASC="TH.BR" ISO="31" FIPS="TH28" REG="NE" />
<Province NameUs="Chachoengsao" nameth="ฉะเชิงเทรา" HASC="TH.CC" ISO="24" FIPS="TH44" REG="C" />
<Province NameUs="Chai Nat" nameth="ชัยนาท" HASC="TH.CN" ISO="18" FIPS="TH32" REG="C" />
<Province NameUs="Chaiyaphum" nameth="ชัยภูมิ" HASC="TH.CY" ISO="36" FIPS="TH26" REG="NE" />
<Province NameUs="Chanthaburi" nameth="จันทบุรี" HASC="TH.CT" ISO="22" FIPS="TH48" REG="E" />
<Province NameUs="Chiang Mai" nameth="เชียงใหม่" HASC="TH.CM" ISO="50" FIPS="TH02" REG="N" />
<Province NameUs="Chiang Rai" nameth="เชียงราย" HASC="TH.CR" ISO="57" FIPS="TH03" REG="N" />
<Province NameUs="Chon Buri" nameth="ชลบุรี" HASC="TH.CB" ISO="20" FIPS="TH46" REG="E" />
<Province NameUs="Chumphon" nameth="ชุมพร" HASC="TH.CP" ISO="86" FIPS="TH58" REG="S" />
<Province NameUs="Kalasin" nameth="กาฬสินธุ์" HASC="TH.KL" ISO="46" FIPS="TH23" REG="NE" />
<Province NameUs="Kamphaeng Phet" nameth="กำแพงเพชร" HASC="TH.KP" ISO="62" FIPS="TH11" REG="N" />
<Province NameUs="Kanchanaburi" nameth="กาญจนบุรี" HASC="TH.KN" ISO="71" FIPS="TH50" REG="C" />
<Province NameUs="Khon Kaen" nameth="ขอนแก่น" HASC="TH.KK" ISO="40" FIPS="TH22" REG="NE" />
<Province NameUs="Krabi" nameth="กระบี่" HASC="TH.KR" ISO="81" FIPS="TH63" REG="S" />
<Province NameUs="Lampang" nameth="ลำปาง" HASC="TH.LG" ISO="52" FIPS="TH06" REG="N" />
<Province NameUs="Lamphun" nameth="ลำพูน" HASC="TH.LN" ISO="51" FIPS="TH05" REG="N" />
<Province NameUs="Loei" nameth="เลย" HASC="TH.LE" ISO="42" FIPS="TH18" REG="NE" />
<Province NameUs="Lop Buri" nameth="ลพบุรี" HASC="TH.LB" ISO="16" FIPS="TH34" REG="C" />
<Province NameUs="Mae Hong Son" nameth="แม่ฮ่องสอน" HASC="TH.MH" ISO="58" FIPS="TH01" REG="N" />
<Province NameUs="Maha Sarakham" nameth="มหาสารคาม" HASC="TH.MS" ISO="44" FIPS="TH24" REG="NE" />
<Province NameUs="Mukdahan" nameth="มุกดาหาร" HASC="TH.MD" ISO="49" FIPS="TH78" REG="NE" />
<Province NameUs="Nakhon Nayok" nameth="นครนายก" HASC="TH.NN" ISO="26" FIPS="TH43" REG="C" />
<Province NameUs="Nakhon Pathom" nameth="นครปฐม" HASC="TH.NP" ISO="73" FIPS="TH53" REG="C" />
<Province NameUs="Nakhon Phanom" nameth="นครพนม" HASC="TH.NF" ISO="48" FIPS="TH73" REG="NE" />
<Province NameUs="Nakhon Ratchasima" nameth="นครราชสีมา" HASC="TH.NR" ISO="30" FIPS="TH27" REG="NE" />
<Province NameUs="Nakhon Sawan" nameth="นครสวรรค์" HASC="TH.NS" ISO="60" FIPS="TH16" REG="N" />
<Province NameUs="Nakhon Si Thammarat" nameth="นครศรีธรรมราช" HASC="TH.NT" ISO="80" FIPS="TH64" REG="S" />
<Province NameUs="Nan" nameth="น่าน" HASC="TH.NA" ISO="55" FIPS="TH04" REG="N" />
<Province NameUs="Narathiwat" nameth="นราธิวาส" HASC="TH.NW" ISO="96" FIPS="TH31" REG="S" />
<Province NameUs="Nong Bua Lam Phu" nameth="ภูหนองบัวลำ" HASC="TH.NB" ISO="39" FIPS="TH79" REG="NE" />
<Province NameUs="Nong Khai" nameth="หนองคาย" HASC="TH.NH" ISO="43" FIPS="TH17" REG="NE" />
<Province NameUs="Nonthaburi" nameth="นนทบุรี" HASC="TH.NO" ISO="12" FIPS="TH38" REG="C" />
<Province NameUs="Pathum Thani" nameth="ปทุมธานี" HASC="TH.PT" ISO="13" FIPS="TH39" REG="C" />
<Province NameUs="Pattani" nameth="ปัตตานี" HASC="TH.PI" ISO="94" FIPS="TH69" REG="S" />
<Province NameUs="Phangnga" nameth="พังงา" HASC="TH.PG" ISO="82" FIPS="TH61" REG="S" />
<Province NameUs="Phatthalung" nameth="พัทลุง" HASC="TH.PL" ISO="93" FIPS="TH66" REG="S" />
<Province NameUs="Phayao" nameth="พะเยา" HASC="TH.PY" ISO="56" FIPS="TH41" REG="N" />
<Province NameUs="Phetchabun" nameth="เพชรบูรณ์" HASC="TH.PH" ISO="67" FIPS="TH14" REG="N" />
<Province NameUs="Phetchaburi" nameth="เพชรบุรี" HASC="TH.PE" ISO="76" FIPS="TH56" REG="C" />
<Province NameUs="Phichit" nameth="พิจิตร" HASC="TH.PC" ISO="66" FIPS="TH13" REG="N" />
<Province NameUs="Phitsanulok" nameth="พิษณุโลก" HASC="TH.PS" ISO="65" FIPS="TH12" REG="N" />
<Province NameUs="Phrae" nameth="แพร่" HASC="TH.PR" ISO="54" FIPS="TH07" REG="N" />
<Province NameUs="Phra Nakhon Si Ayutthaya" nameth="พระนครศรีอยุธยา" HASC="TH.PA" ISO="14" FIPS="TH36" REG="C" />
<Province NameUs="Phuket" nameth="ภูเก็ต" HASC="TH.PU" ISO="83" FIPS="TH62" REG="S" />
<Province NameUs="Prachin Buri" nameth="ปราจีนบุรี" HASC="TH.PB" ISO="25" FIPS="TH74" REG="C" />
<Province NameUs="Prachuap Khiri Khan" nameth="ประจวบคีรีขันธ์" HASC="TH.PK" ISO="77" FIPS="TH57" REG="C" />
<Province NameUs="Ranong" nameth="ระนอง" HASC="TH.RN" ISO="85" FIPS="TH59" REG="S" />
<Province NameUs="Ratchaburi" nameth="ราชบุรี" HASC="TH.RT" ISO="70" FIPS="TH52" REG="C" />
<Province NameUs="Rayong" nameth="ระยอง" HASC="TH.RY" ISO="21" FIPS="TH47" REG="E" />
<Province NameUs="Roi Et" nameth="ร้อยเอ็ด" HASC="TH.RE" ISO="45" FIPS="TH25" REG="NE" />
<Province NameUs="Sa Kaeo" nameth="แก้วสา" HASC="TH.SK" ISO="27" FIPS="TH80" REG="C" />
<Province NameUs="Sakon Nakhon" nameth="สกลนคร" HASC="TH.SN" ISO="47" FIPS="TH20" REG="NE" />
<Province NameUs="Samut Prakan" nameth="สมุทรปราการ" HASC="TH.SP" ISO="11" FIPS="TH42" REG="C" />
<Province NameUs="Samut Sakhon" nameth="สมุทรสาคร" HASC="TH.SS" ISO="74" FIPS="TH55" REG="C" />
<Province NameUs="Samut Songkhram" nameth="สมุทรสงคราม" HASC="TH.SM" ISO="75" FIPS="TH54" REG="C" />
<Province NameUs="Saraburi" nameth="สระบุรี" HASC="TH.SR" ISO="19" FIPS="TH37" REG="C" />
<Province NameUs="Satun" nameth="สตูล" HASC="TH.SA" ISO="91" FIPS="TH67" REG="S" />
<Province NameUs="Sing Buri" nameth="สิงห์บุรี" HASC="TH.SB" ISO="17" FIPS="TH33" REG="C" />
<Province NameUs="Si Sa Ket" nameth="ศรีสะเกษ" HASC="TH.SI" ISO="33" FIPS="TH30" REG="NE" />
<Province NameUs="Songkhla" nameth="สงขลา" HASC="TH.SG" ISO="90" FIPS="TH68" REG="S" />
<Province NameUs="Sukhothai" nameth="สุโขทัย" HASC="TH.SO" ISO="64" FIPS="TH09" REG="N" />
<Province NameUs="Suphan Buri" nameth="สุพรรณบุรี" HASC="TH.SH" ISO="72" FIPS="TH51" REG="C" />
<Province NameUs="Surat Thani" nameth="สุราษฎร์ธานี" HASC="TH.ST" ISO="84" FIPS="TH60" REG="S" />
<Province NameUs="Surin" nameth="สุรินทร์" HASC="TH.SU" ISO="32" FIPS="TH29" REG="NE" />
<Province NameUs="Tak" nameth="ตาก" HASC="TH.TK" ISO="63" FIPS="TH08" REG="N" />
<Province NameUs="Trang" nameth="ตรัง" HASC="TH.TG" ISO="92" FIPS="TH65" REG="S" />
<Province NameUs="Trat" nameth="ตราด" HASC="TH.TT" ISO="23" FIPS="TH49" REG="E" />
<Province NameUs="Ubon Ratchathani" nameth="อุบลราชธานี" HASC="TH.UR" ISO="34" FIPS="TH75" REG="NE" />
<Province NameUs="Udon Thani" nameth="อุดรธานี" HASC="TH.UN" ISO="41" FIPS="TH76" REG="NE" />
<Province NameUs="Uthai Thani" nameth="อุทัยธานี" HASC="TH.UT" ISO="61" FIPS="TH15" REG="N" />
<Province NameUs="Uttaradit" nameth="อุตรดิตถ์" HASC="TH.UD" ISO="53" FIPS="TH10" REG="N" />
<Province NameUs="Yala" nameth="ยะลา" HASC="TH.YL" ISO="95" FIPS="TH70" REG="S" />
<Province NameUs="Yasothon" nameth="ยโสธร" HASC="TH.YS" ISO="35" FIPS="TH72" REG="NE" />
</provinces>
'
CREATE TABLE #Provinces
(
[NameUs] [nvarchar](200) NULL,
[NameTh] [nvarchar](200) NULL,
[HASC] [nvarchar](200) NULL,
[ISO] [nvarchar](200) NULL,
[FIPS] [nvarchar](200) NULL,
[REG] [nvarchar](200) NULL
)
INSERT INTO #Provinces ([NameUs], [NameTh],[HASC],[ISO],[FIPS],[REG])
SELECT
nref.value('@NameUs', 'nvarchar(200)'),
nref.value('@nameth', 'nvarchar(200)'),
nref.value('@HASC', 'nvarchar(200)'),
nref.value('@ISO', 'nvarchar(200)'),
nref.value('@FIPS', 'nvarchar(200)'),
nref.value('@REG', 'nvarchar(200)')
FROM @provinces.nodes('//provinces/Province') AS R(nref)
--SELECT * FROM #Provinces
DECLARE @UsLanguageId int; SET @UsLanguageId = (SELECT TOP 1 [Id] FROM Language WHERE LanguageCulture = 'en-US')
DECLARE @ThLanguageId int; SET @ThLanguageId = (SELECT TOP 1 [Id] FROM Language WHERE LanguageCulture = 'th-TH')
DECLARE @ThailandId int; SET @ThailandId = (SELECT TOP 1 [Id] FROM Country WHERE ThreeLetterIsoCode = 'THA')
DECLARE cur_region CURSOR FOR
SELECT [NameUs], [NameTh],[HASC],[ISO],[FIPS],[REG]
FROM #Provinces
DECLARE @NameUs nvarchar(200);
DECLARE @NameTh nvarchar(200);
DECLARE @HASC nvarchar(200);
DECLARE @ISO nvarchar(200);
DECLARE @FIPS nvarchar(200);
DECLARE @REG nvarchar(200);
OPEN cur_region
FETCH NEXT FROM cur_region INTO @NameUs,@NameTh,@HASC,@ISO,@FIPS,@REG
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @StateProvinceId int;
SET @StateProvinceId = (SELECT [Id] FROM StateProvince WHERE CountryId = @ThailandId AND Abbreviation = @HASC)
IF (@StateProvinceId>0)
BEGIN
UPDATE [StateProvince] SET [Name] = @NameUs WHERE Id = @StateProvinceId;
END
ELSE
BEGIN
INSERT INTO [StateProvince]
([CountryId]
,[Name]
,[Abbreviation]
,[Published]
,[DisplayOrder])
VALUES
(
@ThailandId
,@NameUs
,@HASC
,1
,1);
SET @StateProvinceId = SCOPE_IDENTITY();
END
IF (@StateProvinceId>0 AND @ThLanguageId>0)
BEGIN
DECLARE @LocalizedPropertyId int;
SET @LocalizedPropertyId = (SELECT Id FROM LocalizedProperty WHERE LanguageId = @ThLanguageId AND LocaleKeyGroup = 'StateProvince' AND LocaleKey = 'Name' AND EntityId = @StateProvinceId)
IF (@LocalizedPropertyId>0)
BEGIN
UPDATE LocalizedProperty SET LocaleValue = @NameTh WHERE Id = @LocalizedPropertyId;
END
ELSE
BEGIN
INSERT INTO LocalizedProperty
(
EntityId,
LanguageId,
LocaleKeyGroup,
LocaleKey,
LocaleValue
)
VALUES
(
@StateProvinceId,
@ThLanguageId,
'StateProvince',
'Name',
@NameTh
);
END
END
--fetch next language identifier
FETCH NEXT FROM cur_region INTO @NameUs,@NameTh,@HASC,@ISO,@FIPS,@REG
END
CLOSE cur_region
DEALLOCATE cur_region
DROP TABLE #Provinces
GO