使用存储过程导入excel数据到sql server中,更新需要更新的项
首先导入到临时表中
存储过程只能从查询分析器中建立,不能在企业管理器中新建存储过程
否则会出现ANSI_NULLS,ANSI_WARNINGS的郁闷问题
CREATE PROCEDURE P_ImportExcel
@path_file varchar(200)
AS
create table #Student(
zjzh char(12) null , -- 资金帐号
zjxm varchar(20) null , -- 资金姓名
sfzh varchar(20) null , -- 身份证号码
dhhm varchar(20) null , -- 电话号码
yzbm varchar(6) null , -- 邮政编码
txdz varchar(80) null , -- 通讯地址
email varchar(40) null , -- Email地址
bp varchar(20) null , -- BP机号码
jjrdm varchar(16) null , -- 班级
)
--set @path_file = 'c:/student.xls'
declare @s varchar(1000)
set @s ='''Microsoft.Jet.OLEDB.4.0'',
''Data Source="'+@path_file+'";Extended properties=Excel 5.0'''
set @s = 'insert into #Student SELECT 资金帐号,资金姓名,身份证号码,电话号码,邮政编码,通讯地址,Email地址,BP机号码,班级 FROM OpenDataSource ('+@s+')...[sheet1$]'
exec(@s)
select * from #Student
update zjzlk set zjzlk.zjxm=case when #Student.zjxm is null then '' else #Student.zjxm end,
zjzlk.sfzh=case when #Student.sfzh is null then '' else #Student.sfzh end,
zjzlk.dhhm=case when #Student.dhhm is null then '' else #Student.dhhm end,
zjzlk.yzbm=case when #Student.yzbm is null then '' else #Student.yzbm end,
zjzlk.txdz=case when #Student.txdz is null then '' else #Student.txdz end,
zjzlk.email=case when #Student.email is null then '' else #Student.email end,
zjzlk.bp=case when #Student.bp is null then '' else #Student.bp end,
zjzlk.jjrdm=case when #Student.jjrdm is null then '' else #Student.jjrdm end
from zjzlk,#Student
where zjzlk.zjzh=#Student.zjzh
GO