use ziranzaihai
SELECT * into liutonado
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\zrzh\yyyy-tonado.xls;HDR=YES;IMEX=1', sheet1$ )
select 数据来源,灾害类型,灾害发生年,灾害发生月,灾害发生日,灾害开始时间,灾害结束时间,省_Name as 观测省,市_Name as 观测市,县_Name as 观测县,观测站名,观测站号,乡镇_Name as 发生地,a.value,POINT_X as 经度,POINT_Y as 纬度 ,
大风等级,受伤人数,死亡人数,经济损失,农作物损失
into windtemp
from dbo.liuwind a ,dbo.Town_New b
where b.乡镇_Name like a.发生地+'%'
and b.县_Name like '%'+a. 观测县+'%'
select 数据来源,灾害类型,灾害发生年,灾害发生月,灾害发生日,灾害开始时间,灾害结束时间,省_Name as 观测省,市_Name as 观测市,县_Name as 观测县,观测站名,观测站号,乡镇_Name as 发生地,a.value,POINT_X as 经度,POINT_Y as 纬度 ,
大风等级,受伤人数,死亡人数,经济损失,农作物损失
into windtemp2
from dbo.liuwind a ,dbo.Town_New b
where b.乡镇_Name like a.发生地+'%'
and b.市_Name like a.观测市+'%'
select * from windtemp
insert into windtemp
select * from windtemp2 where 发生地 not in (select 发生地 from windtemp)
select * into yyyy_wind from windtemp
select 数据来源,灾害类型,灾害发生年,灾害发生月,灾害发生日,开始时间 as 灾害开始时间,结束时间 as 灾害结束时间,省_Name as 观测省,市_Name as 观测市,县_Name as 观测县,观测站名,观测站号,乡镇_Name as 发生地,测站风速,POINT_X as 经度,POINT_Y as 纬度 ,
受伤人数,死亡人数,经济损失,农业损失
into tonadotemp
from dbo.liutonado a ,dbo.Town_New b
where b.乡镇_Name like a.灾害发生地点+'%'
and b.县_Name like '%'+a. 观测县+'%'
select 数据来源,灾害类型,灾害发生年,灾害发生月,灾害发生日,开始时间 as 灾害开始时间,结束时间 as 灾害结束时间,省_Name as 观测省,市_Name as 观测市,县_Name as 观测县,观测站名,观测站号,乡镇_Name as 发生地,测站风速,POINT_X as 经度,POINT_Y as 纬度 ,
受伤人数,死亡人数,经济损失,农业损失
into tonadotemp2
from dbo.liutonado a ,dbo.Town_New b
where b.乡镇_Name like a.灾害发生地点+'%'
and b.市_Name like a.观测市+'%'
select * from tonadotemp
insert into tonadotemp
select * from tonadotemp2 where 发生地 not in (select 发生地 from tonadotemp)
-------------增加id列
-----alter table test add id int identity(1,1)
---------
----------转换时间列（这里excel列全部为DateTime类型）
alter table tonadotemp add 开始时间 varchar(255)
alter table tonadotemp add 结束时间 varchar(255)
update tonadotemp set 开始时间=CONVERT(varchar,灾害开始时间,108)
update tonadotemp set 结束时间=CONVERT(varchar,灾害结束时间,108)
alter table tonadotemp drop column 灾害开始时间
alter table tonadotemp drop column 灾害结束时间
select * from yyyy_wind
select * from tonadotemp where 观测站名 =''
select * from tonadotemp
where 经济损失 Is Not NULL
and 农业损失 IS NULL
-----------------讲写在一起的yyyy-mm-dd 分开
select datepart (year,column_name) from Table_name
select datepart (month,column_name) from Table_name
select datepart (day,column_name) from Table_name
------------------------------------------------------
---------------------补充----------------------
---------------------------------------------------
select getdate()
2004-09-12 11:06:08.177
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
