全国省市SQL数据表.docx

1、全国省市SQL数据表drop table TProvinceAndCityGOCreate Table TProvinceAndCity(PKey int identity(1,1) not null,Province nvarchar(32) null,City nvarchar(32) null)GoBEGIN TRANSACTION ;SELECT TOP 1 0 FROM TProvinceAndCity WITH(HOLDLOCK , TABLOCK) ;DISABLE TRIGGER ALL ON TProvinceAndCity ;SET IDENTITY_INSERT TPro

2、vinceAndCity ON ;BEGIN TRYIF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 1)UPDATE TProvinceAndCity SET Province=N北京市,City=N西城区 WHERE Pkey = 1 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(1,N北京市,N西城区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 2)UPDATE TProvin

3、ceAndCity SET Province=N北京市,City=N崇文区 WHERE Pkey = 2 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(2,N北京市,N崇文区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 3)UPDATE TProvinceAndCity SET Province=N北京市,City=N宣武区 WHERE Pkey = 3 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City)

4、VALUES(3,N北京市,N宣武区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 4)UPDATE TProvinceAndCity SET Province=N北京市,City=N朝阳区 WHERE Pkey = 4 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(4,N北京市,N朝阳区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 5)UPDATE TProvinceAndC

5、ity SET Province=N北京市,City=N丰台区 WHERE Pkey = 5 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(5,N北京市,N丰台区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 6)UPDATE TProvinceAndCity SET Province=N北京市,City=N石景山区 WHERE Pkey = 6 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUE

6、S(6,N北京市,N石景山区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 7)UPDATE TProvinceAndCity SET Province=N北京市,City=N海淀区 WHERE Pkey = 7 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(7,N北京市,N海淀区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 8)UPDATE TProvinceAndCity

7、SET Province=N北京市,City=N门头沟区 WHERE Pkey = 8 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(8,N北京市,N门头沟区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 9)UPDATE TProvinceAndCity SET Province=N北京市,City=N房山区 WHERE Pkey = 9 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(9

8、,N北京市,N房山区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 10)UPDATE TProvinceAndCity SET Province=N北京市,City=N通州区 WHERE Pkey = 10 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(10,N北京市,N通州区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 11)UPDATE TProvinceAndCity

9、SET Province=N北京市,City=N顺义区 WHERE Pkey = 11 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(11,N北京市,N顺义区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 12)UPDATE TProvinceAndCity SET Province=N北京市,City=N昌平区 WHERE Pkey = 12 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES

10、(12,N北京市,N昌平区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 13)UPDATE TProvinceAndCity SET Province=N北京市,City=N大兴区 WHERE Pkey = 13 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(13,N北京市,N大兴区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 14)UPDATE TProvinceAndCi

11、ty SET Province=N北京市,City=N怀柔区 WHERE Pkey = 14 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(14,N北京市,N怀柔区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 15)UPDATE TProvinceAndCity SET Province=N北京市,City=N平谷区 WHERE Pkey = 15 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VAL

12、UES(15,N北京市,N平谷区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 16)UPDATE TProvinceAndCity SET Province=N北京市,City=N密云县 WHERE Pkey = 16 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(16,N北京市,N密云县);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 17)UPDATE TProvinceAn

13、dCity SET Province=N北京市,City=N延庆县 WHERE Pkey = 17 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(17,N北京市,N延庆县);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 18)UPDATE TProvinceAndCity SET Province=N上海市,City=N黄浦区 WHERE Pkey = 18 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City)

14、VALUES(18,N上海市,N黄浦区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 19)UPDATE TProvinceAndCity SET Province=N上海市,City=N卢湾区 WHERE Pkey = 19 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(19,N上海市,N卢湾区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey = 20)UPDATE TProvinceAndCity SET Province=N上海市,City=N徐汇区 WHERE Pkey = 20 ;ELSEINSERT TProvinceAndCity(Pkey,Province,City) VALUES(20,N上海市,N徐汇区);IF EXISTS(SELECT TOP 1 0 FROM TProvinceAndCity WHERE Pkey =