以下代碼將c:\\cs\\文件夾下的全部Excle中數據導入到SQL數據庫
declare @query vARCHAR(1000)
declare @max1 int
declare @count1 int
declare @filename varchar(100)
set @count1=0
create table #x(name varchar(200))
insert #x exec('master.dbo.xp_cmdshell ''dir c:\\cs\\*.xls /b''')
delete from #x where name is null
select IDENTITY(int,1,1) as id ,name into #y from #x
drop table #x
set @max1=(select MAX(id) from #y)
while @count1 <= @max1
begin
set @count1=@count1+1
set @filename=(select name from #y where id=@count1)
set @query='SELECT * INTO dbo.tab9 FROM OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;Database=c:\\cs\\'+@filename+''',''select * from [1月份$b11:e27]'')'
print @query
EXEC @query
end
drop table #y
go
TO dbo.tab9 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;Database=c:\\cs\\上海.xls','select * from [1月份$b11:e27]')
SELECT * INTO dbo.tab9 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;Database=c:\\cs\\上海.xls','select * from [1月份$b11:e27]')
EXEC ( @query)