将excel中的数据导入到Server数据表

--首先将excel中的所有字段信息及记录信息导入到对应的新表aaaa中；
--向zckc表中插入aaaa表中中的数据（对于一些类型冲突的字段，另外处理）
insert into zckc
(zcbh,bmmc,bmbm,p3,zclablelbbm,zcmc,ty_gg,bz,zcsl,dwje,ty_lyr,p1,fy_gb,ghdwmc,lzzt,rkfs,rksj,fwjzmj)
select
id,bmmc,bmbm,p3,zclablelbbm,zcmc,ty_gg,bz,zcsl,dwje,ty_lyr,p1,fy_gb,ghdwmc,lzzt,rkfs,rksj,fwjzmj from aaaa;
--查找时间不规范的数据
select * from aaaa where convert(int,right(ty_ccrq,2))>12 or len(ty_ccrq)<>7
--将所有的不规范的日期数据设置为null值
update aaaa set grsj=null where grsj in (select grsj from aaaa where convert(int,right(grsj,2))>12 or len(grsj)<>7);
--更新购入时间
update zckc set grsj = (select convert(datetime,left(grsj,4)+'-'+right(grsj,2)+'-01',120) from aaaa where id = zckc.zcbh);
--更新出厂日期
update zckc set ty_ccrq = (select convert(datetime,left(ty_ccrq,4)+'-'+right(ty_ccrq,2)+'-01',120) from aaaa where id = zckc.zcbh);
--按照约束规则生成资产编号zclabel
update zckc set zclabel = (select '132009'+right('0000'+convert(varchar,id),5) from aaaa where id = zckc.zcbh)
--查询所有设备来源方式rkfs---------1 代表“自建” 2代表“购买” 3代表“无偿调入”
select distinct rkfs from zckc;
--更改设备来源方式
update zckc set rkfs = '自建' where rkfs = '1';
update zckc set rkfs = '购买' where rkfs = '2';
update zckc set rkfs = '无偿调入' where rkfs = '3';
--将设备来源方式为null的先暂时设为‘1’
update zckc set rkfs = '1' where rkfs is null;
--查询所有的资产现状lzzt-------1代表“在用”， B 代表“非转经”
select distinct lzzt from zckc;
---更改资产现状中文为编号----------“在用”改为‘7’，“非转经”改为‘E’，‘1’也改为7
update zckc set lzzt = '7' where lzzt='1' or lzzt is null;
update zckc set lzzt = 'E' where lzzt = 'B';
---查询所有不同的jsfs
select distinct jsfs from zckc;
--更改jsfs都设为‘1’
update zckc set jsfs='1';
---更新资产条码
update zckc set zclablelbbm = (substring(zclablelbbm,2,len(zclablelbbm)));
--规范资产条码
update zckc set zclablelbbm = (select convert(varchar,convert(varchar,type)+SortCode) from Asort where SortCode = zckc.zclablelbbm);
---更新资产类别编码
update zckc set zclbbm = (select zclablelbbm from aaaa where id = zckc.zcbh );