-- 将文件夹下的所有EXCEL导入SQL数据库中,每一文件为一单独的表,表名为文件名 -- By MadGoat 2006-11-23 create table #t (fname varchar ( 260 ),depth int ,isf bit ) insert into #t exec master..xp_dirtree ' c:\test\ ' , 1 , 1 declare tb cursor for select fn = ' c:\test\ ' + fname from #t where isf = 1 and fname like ' %.xls ' -- 取.xls文件(EXCEL) declare @fn varchar ( 8000 ) declare @fname varchar ( 8000 ) declare @end int -- 截取的结束位置 declare @fnamelen int -- 文件名称长度 open tb fetch next from tb into @fn while @@fetch_status = 0 begin set @end = len ( @fn ) - PATINDEX ( ' %\% ' , reverse ( @fn )) set @fnamelen = len ( @fn ) - 5 - @end set @fname = SUBSTRING ( @fn , @end + 2 , @fnamelen ) set @fn = ' SELECT * into ' + @fname + ' FROM OPENDATASOURCE( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel 5.0;DATABASE= ' + @fn + ''' )[Sheet1$] ' -- 关键是这句 exec ( @fn ) fetch next from tb into @fn end close tb deallocate tb drop table #t 转载于:https://www.cnblogs.com/madgoat/archive/2006/11/23/570039.html