Import Administrative Region to SQL Server

The procedure is as follows:

1. Go to the website of the National Bureau of Statistics and find the administrative districts of counties and above.

I found this: http://www.stats.gov.cn/tjbz/xzqhdm/t20130118_402867249.htm

Copy and paste the code on the page to notepad and save it as E: \ temp \ region.txt.

(Note that the replication may be subject to memory restrictions. It may not be possible to copy all the content at a time. Check the content .)

Copy and paste the result, so that I am dumb:

It is actually a line of the zip code and name respectively. Manually change to a row? Write a program to correct it? All mosquitoes are asleep.

2. Use SQL Server Management studio to export data.

Note that you must set an ID column in the target table.

3. Generate a region table

The destination table imported here is a temporary intermediate table: region_tmp. What I really want is the region table.

Structure of region_tmp in the intermediate table:

Region_tmp
======================================
Id int identity (1, 1)
CN varchar (50)

After the import, the data is as follows:

The region table I want is region.

Region
======================================
Id int identity (1, 1)
Code char (6)
Name nvarchar (50)
Parentid int

So now we need to move the data from region_tmp => region.

During import, you need to set an ID column to distinguish zip code and place name. In region_tmp, the odd lines are both encoded and the even lines are place names. Therefore, you can write the encoding and place names into the same line of region using the following statement:

INSERT INTO [dbo].[Region]           ([code]           ,[name]           ,[parentId])SELECT a.cn,b.cn,0FROM region_tmp a,region_tmp bWHERE b.id=a.id+1and (a.id % 2) = 1

4. Modify the region table

Modify the region. parentid field. That is, each place name record must have a parent ID, which is sorted properly when used. For example, the parent ID of Guangzhou is Guangdong province, and the parent ID of Tianhe District is Guangzhou.

-- Set the parent idupdate [DBO] for city-level place names. [region] Set parentid = B. idfrom [region], [region] bwhere left ([region]. code, 2) = left (B. code, 2) and right ([region]. code, 4) <> '20140901' and right ([region]. code, 2) = '00' and right (B. code, 4) = '000000'; -- sets the parent idupdate [DBO] for county-level place names. [region] Set parentid = B. idfrom [region], [region] bwhere left ([region]. code, 4) = left (B. code, 4) and right ([region]. code, 2 )! = '00' and right (B. Code, 2) = '00 ';

Some records do not conform to the usage habits and are deleted after modification.

Update [DBO]. [region] Set parentid = C. idfrom [DBO]. [region], [DBO]. [region] B, [DBO]. [region] cwhere [DBO]. [region]. parentid = B. ID and B. parentid = C. idand B. name in ('city region', 'Count'); Delete from [DBO]. [region] Where name in ('city region', 'region ');