declare
@i int,
@param int,
@optionstring varchar(1000)
declare @b varchar(2),@files varchar(100)
set @i=1
set @b=''
--先在外围应用配置器中功能的应用里打开即席远程查询.
while (@i <=30)
begin
if (len(@i))=1
set @b = right(100+ @i,2)
else
set @b=@i
------判断文件是否存在
set @files='C:\' + @b +'.xls'
EXECUTE master..xp_fileexist @files, @param OUTPUT
if @param=1 --@param = 0 不存在;@param = 1 存在
begin
set @optionstring='insert into Test select 日期=''20080701'',营销部,客户编码 from OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="c:\'
+ @b + '.xls";User ID=Admin;Password=;Extended properties=Excel 4.0'')...Sheet1$'
exec (@optionstring)
end
set @i=@i+1
end
@i int,
@param int,
@optionstring varchar(1000)
declare @b varchar(2),@files varchar(100)
set @i=1
set @b=''
--先在外围应用配置器中功能的应用里打开即席远程查询.
while (@i <=30)
begin
if (len(@i))=1
set @b = right(100+ @i,2)
else
set @b=@i
------判断文件是否存在
set @files='C:\' + @b +'.xls'
EXECUTE master..xp_fileexist @files, @param OUTPUT
if @param=1 --@param = 0 不存在;@param = 1 存在
begin
set @optionstring='insert into Test select 日期=''20080701'',营销部,客户编码 from OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="c:\'
+ @b + '.xls";User ID=Admin;Password=;Extended properties=Excel 4.0'')...Sheet1$'
exec (@optionstring)
end
set @i=@i+1
end
+@b+'.XLS";Extended Properties="Excel 8.0";Persist Security Info=False'')...[sheet1$]'