省、市、县、镇、村数据库（PowerDesigner设计、BCP导入）

一、PowerDesigner设计设计数据库

代码脚本如下：

if exists (select 1
            from  sysobjects
           where  id = object_id('Static_Position')
            and   type = 'U')
   drop table Static_Position
go

/*==============================================================*/
/* Table: Static_Position                                       */
/*==============================================================*/
create table Static_Position (
   Id                   int                  not null,
   ProvinceId           bigint               null,
   ProvinceName         varchar(50)          null,
   CityId               bigint               null,
   CityName             varchar(50)          null,
   CountyId             bigint               null,
   CountyName           varchar(50)          null,
   TownId               bigint               null,
   TownName             varchar(50)          null,
   VillageId            bigint               null,
   VillageName          varchar(50)          null,
   constraint PK_STATIC_POSITION primary key (Id)
)
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '位置表（省、市、县、镇、村数据）',
   'user', @CurrentUser, 'table', 'Static_Position'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '表主键',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'Id'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '省代码',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'ProvinceId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '省名称',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'ProvinceName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '市代码',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CityId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '市名称（省级市）',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CityName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '乡代码',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CountyId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '乡名称（地级市或县或区）',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CountyName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '镇代码',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'TownId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '镇名称（或街道）',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'TownName'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '村代码',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'VillageId'
go

declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description', 
   '村名称（或社区）',
   'user', @CurrentUser, 'table', 'Static_Position', 'column', 'VillageName'
go

完整的数据库表创建的脚本：

脚本代码：

  1 if exists (select 1
  2             from  sysobjects
  3            where  id = object_id('Static_Position')
  4             and   type = 'U')
  5    drop table Static_Position
  6 go
  7 
  8 /*==============================================================*/
  9 /* Table: Static_Position                                       */
 10 /*==============================================================*/
 11 create table Static_Position (
 12    Id                   int                  not null,
 13    ProvinceId           bigint               null,
 14    ProvinceName         varchar(50)          null,
 15    CityId               bigint               null,
 16    CityName             varchar(50)          null,
 17    CountyId             bigint               null,
 18    CountyName           varchar(50)          null,
 19    TownId               bigint               null,
 20    TownName             varchar(50)          null,
 21    VillageId            bigint               null,
 22    VillageName          varchar(50)          null,
 23    constraint PK_STATIC_POSITION primary key (Id)
 24 )
 25 go
 26 
 27 declare @CurrentUser sysname
 28 select @CurrentUser = user_name()
 29 execute sp_addextendedproperty 'MS_Description', 
 30    '位置表（省、市、县、镇、村数据）',
 31    'user', @CurrentUser, 'table', 'Static_Position'
 32 go
 33 
 34 declare @CurrentUser sysname
 35 select @CurrentUser = user_name()
 36 execute sp_addextendedproperty 'MS_Description', 
 37    '表主键',
 38    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'Id'
 39 go
 40 
 41 declare @CurrentUser sysname
 42 select @CurrentUser = user_name()
 43 execute sp_addextendedproperty 'MS_Description', 
 44    '省代码',
 45    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'ProvinceId'
 46 go
 47 
 48 declare @CurrentUser sysname
 49 select @CurrentUser = user_name()
 50 execute sp_addextendedproperty 'MS_Description', 
 51    '省名称',
 52    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'ProvinceName'
 53 go
 54 
 55 declare @CurrentUser sysname
 56 select @CurrentUser = user_name()
 57 execute sp_addextendedproperty 'MS_Description', 
 58    '市代码',
 59    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CityId'
 60 go
 61 
 62 declare @CurrentUser sysname
 63 select @CurrentUser = user_name()
 64 execute sp_addextendedproperty 'MS_Description', 
 65    '市名称（省级市）',
 66    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CityName'
 67 go
 68 
 69 declare @CurrentUser sysname
 70 select @CurrentUser = user_name()
 71 execute sp_addextendedproperty 'MS_Description', 
 72    '乡代码',
 73    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CountyId'
 74 go
 75 
 76 declare @CurrentUser sysname
 77 select @CurrentUser = user_name()
 78 execute sp_addextendedproperty 'MS_Description', 
 79    '乡名称（地级市或县或区）',
 80    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'CountyName'
 81 go
 82 
 83 declare @CurrentUser sysname
 84 select @CurrentUser = user_name()
 85 execute sp_addextendedproperty 'MS_Description', 
 86    '镇代码',
 87    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'TownId'
 88 go
 89 
 90 declare @CurrentUser sysname
 91 select @CurrentUser = user_name()
 92 execute sp_addextendedproperty 'MS_Description', 
 93    '镇名称（或街道）',
 94    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'TownName'
 95 go
 96 
 97 declare @CurrentUser sysname
 98 select @CurrentUser = user_name()
 99 execute sp_addextendedproperty 'MS_Description', 
100    '村代码',
101    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'VillageId'
102 go
103 
104 declare @CurrentUser sysname
105 select @CurrentUser = user_name()
106 execute sp_addextendedproperty 'MS_Description', 
107    '村名称（或社区）',
108    'user', @CurrentUser, 'table', 'Static_Position', 'column', 'VillageName'
109 go
110 ---------------------------------------------------------------------------------
111 if exists (select 1
112             from  sysobjects
113            where  id = object_id('Static_Position_City')
114             and   type = 'U')
115    drop table Static_Position_City
116 go
117 
118 /*==============================================================*/
119 /* Table: Static_Position_City                                  */
120 /*==============================================================*/
121 create table Static_Position_City (
122    Id                   int                  not null,
123    ProvinceId           int                  null,
124    CityId               bigint               null,
125    CityName             varchar(50)          null,
126    constraint PK_STATIC_POSITION_CITY primary key (Id)
127 )
128 go
129 
130 declare @CurrentUser sysname
131 select @CurrentUser = user_name()
132 execute sp_addextendedproperty 'MS_Description', 
133    '位置表-市(县级市数据库)',
134    'user', @CurrentUser, 'table', 'Static_Position_City'
135 go
136 
137 declare @CurrentUser sysname
138 select @CurrentUser = user_name()
139 execute sp_addextendedproperty 'MS_Description', 
140    '地级市id',
141    'user', @CurrentUser, 'table', 'Static_Position_City', 'column', 'ProvinceId'
142 go
143 
144 declare @CurrentUser sysname
145 select @CurrentUser = user_name()
146 execute sp_addextendedproperty 'MS_Description', 
147    '县级市id',
148    'user', @CurrentUser, 'table', 'Static_Position_City', 'column', 'CityId'
149 go
150 
151 declare @CurrentUser sysname
152 select @CurrentUser = user_name()
153 execute sp_addextendedproperty 'MS_Description', 
154    '县级市名称',
155    'user', @CurrentUser, 'table', 'Static_Position_City', 'column', 'CityName'
156 go
157 --------------------------------------------------------------------------------
158 if exists (select 1
159             from  sysobjects
160            where  id = object_id('Static_Position_County')
161             and   type = 'U')
162    drop table Static_Position_County
163 go
164 
165 /*==============================================================*/
166 /* Table: Static_Position_County                                */
167 /*==============================================================*/
168 create table Static_Position_County (
169    id                   int                  not null,
170    CityId               bigint               null,
171    CountyId             bigint               null,
172    CountyName           varchar(50)          null,
173    constraint PK_STATIC_POSITION_COUNTY primary key (id)
174 )
175 go
176 
177 declare @CurrentUser sysname
178 select @CurrentUser = user_name()
179 execute sp_addextendedproperty 'MS_Description', 
180    '位置表-地区市(地区市数据库)',
181    'user', @CurrentUser, 'table', 'Static_Position_County'
182 go
183 
184 declare @CurrentUser sysname
185 select @CurrentUser = user_name()
186 execute sp_addextendedproperty 'MS_Description', 
187    '地级市主键Id',
188    'user', @CurrentUser, 'table', 'Static_Position_County', 'column', 'id'
189 go
190 
191 declare @CurrentUser sysname
192 select @CurrentUser = user_name()
193 execute sp_addextendedproperty 'MS_Description', 
194    '地级市id',
195    'user', @CurrentUser, 'table', 'Static_Position_County', 'column', 'CityId'
196 go
197 
198 declare @CurrentUser sysname
199 select @CurrentUser = user_name()
200 execute sp_addextendedproperty 'MS_Description', 
201    '县级id',
202    'user', @CurrentUser, 'table', 'Static_Position_County', 'column', 'CountyId'
203 go
204 
205 declare @CurrentUser sysname
206 select @CurrentUser = user_name()
207 execute sp_addextendedproperty 'MS_Description', 
208    '县级名称',
209    'user', @CurrentUser, 'table', 'Static_Position_County', 'column', 'CountyName'
210 go
211 ----------------------------------------------------------------------------------
212 if exists (select 1
213             from  sysobjects
214            where  id = object_id('Static_Position_Village')
215             and   type = 'U')
216    drop table Static_Position_Village
217 go
218 
219 /*==============================================================*/
220 /* Table: Static_Position_Village                               */
221 /*==============================================================*/
222 create table Static_Position_Village (
223    id                   int                  not null,
224    TownId               bigint               null,
225    VillageId            bigint               null,
226    VillageName          varchar(50)          null,
227    constraint PK_STATIC_POSITION_VILLAGE primary key (id)
228 )
229 go
230 
231 declare @CurrentUser sysname
232 select @CurrentUser = user_name()
233 execute sp_addextendedproperty 'MS_Description', 
234    '位置表-镇村(省市县镇村数据库)',
235    'user', @CurrentUser, 'table', 'Static_Position_Village'
236 go
237 
238 declare @CurrentUser sysname
239 select @CurrentUser = user_name()
240 execute sp_addextendedproperty 'MS_Description', 
241    '镇id',
242    'user', @CurrentUser, 'table', 'Static_Position_Village', 'column', 'TownId'
243 go
244 
245 declare @CurrentUser sysname
246 select @CurrentUser = user_name()
247 execute sp_addextendedproperty 'MS_Description', 
248    '村id--唯一',
249    'user', @CurrentUser, 'table', 'Static_Position_Village', 'column', 'VillageId'
250 go
251 
252 declare @CurrentUser sysname
253 select @CurrentUser = user_name()
254 execute sp_addextendedproperty 'MS_Description', 
255    '村名称',
256    'user', @CurrentUser, 'table', 'Static_Position_Village', 'column', 'VillageName'
257 go
258 -----------------------------------------------------------------------
259 if exists (select 1
260             from  sysobjects
261            where  id = object_id('Static_Position_Provice')
262             and   type = 'U')
263    drop table Static_Position_Provice
264 go
265 
266 /*==============================================================*/
267 /* Table: Static_Position_Provice                               */
268 /*==============================================================*/
269 create table Static_Position_Provice (
270    Id                   id                   not null,
271    ProviceId            bigint               null,
272    ProviceName          varchar(50)          null,
273    constraint PK_STATIC_POSITION_PROVICE primary key (Id)
274 )
275 go
276 
277 declare @CurrentUser sysname
278 select @CurrentUser = user_name()
279 execute sp_addextendedproperty 'MS_Description', 
280    '位置表-省(省份数据库）',
281    'user', @CurrentUser, 'table', 'Static_Position_Provice'
282 go
283 
284 declare @CurrentUser sysname
285 select @CurrentUser = user_name()
286 execute sp_addextendedproperty 'MS_Description', 
287    '主键ID',
288    'user', @CurrentUser, 'table', 'Static_Position_Provice', 'column', 'Id'
289 go
290 
291 declare @CurrentUser sysname
292 select @CurrentUser = user_name()
293 execute sp_addextendedproperty 'MS_Description', 
294    '省份id、省份编号',
295    'user', @CurrentUser, 'table', 'Static_Position_Provice', 'column', 'ProviceId'
296 go
297 
298 declare @CurrentUser sysname
299 select @CurrentUser = user_name()
300 execute sp_addextendedproperty 'MS_Description', 
301    '省份名称',
302    'user', @CurrentUser, 'table', 'Static_Position_Provice', 'column', 'ProviceName'
303 go
304 -----------------------------------------------------------
305 if exists (select 1
306             from  sysobjects
307            where  id = object_id('Static_Position_Town')
308             and   type = 'U')
309    drop table Static_Position_Town
310 go
311 
312 /*==============================================================*/
313 /* Table: Static_Position_Town                                  */
314 /*==============================================================*/
315 create table Static_Position_Town (
316    Id                   int                  not null,
317    CountyId             bigint               null,
318    TownId               bigint               null,
319    TownName             varchar(50)          null,
320    constraint PK_STATIC_POSITION_TOWN primary key (Id)
321 )
322 go
323 
324 declare @CurrentUser sysname
325 select @CurrentUser = user_name()
326 execute sp_addextendedproperty 'MS_Description', 
327    '位置表-镇(镇数据库）',
328    'user', @CurrentUser, 'table', 'Static_Position_Town'
329 go
330 
331 declare @CurrentUser sysname
332 select @CurrentUser = user_name()
333 execute sp_addextendedproperty 'MS_Description', 
334    '县级市id',
335    'user', @CurrentUser, 'table', 'Static_Position_Town', 'column', 'CountyId'
336 go
337 
338 declare @CurrentUser sysname
339 select @CurrentUser = user_name()
340 execute sp_addextendedproperty 'MS_Description', 
341    '镇id',
342    'user', @CurrentUser, 'table', 'Static_Position_Town', 'column', 'TownId'
343 go
344 
345 declare @CurrentUser sysname
346 select @CurrentUser = user_name()
347 execute sp_addextendedproperty 'MS_Description', 
348    '镇名称',
349    'user', @CurrentUser, 'table', 'Static_Position_Town', 'column', 'TownName'
350 go

View Code

二、BCP的导入

　　1、启动

-- 允许配置高级选项  
EXEC master.sys.sp_configure 'show advanced options', 1  
-- 重新配置  
RECONFIGURE  
-- 启用xp_cmdshell  
EXEC master.sys.sp_configure 'xp_cmdshell', 1  
--重新配置  
RECONFIGURE

　　2、测试导出

EXEC [master]..xp_cmdshell
'BCP [数据库名].dbo.Static_Area out E:9.txt -c -T'
GO

　　3、导入省、市、县、镇、村数据

EXEC master..xp_cmdshell
'BCP [数据库名称].dbo.Static_Position in E:8.xml -c -T'
GO

　　4、关闭

-- 允许配置高级选项  
EXEC master.sys.sp_configure 'show advanced options', 0
-- 重新配置  
RECONFIGURE  
-- 启用xp_cmdshell  
EXEC master.sys.sp_configure 'xp_cmdshell', 0
--重新配置  
RECONFIGURE

三、完整的下载目录

下载地址：省市县镇村数据

相关阅读:
优化网站性能的14条准则
 单元测试
 无配置wcf Host
.net 4.0
Java 7 resources
关于重用
 用GMaven plugin更好地编译系统
 关于系统分层的自问自答
 UBIQUITOUS LANAGUAGE
用Groovy方式实现接口便于单元测试和协作开发
原文地址：https://www.cnblogs.com/eadily-dream/p/5519360.html