--如果匯入資料並生成表

select * into 表 from

OPENROWSET("MICROSOFT.JET.OLEDB.4.0"

,"Excel 5.0;HDR=YES;DATABASE=c:\test.xls",sheet1$)

===

--將某個目錄上的Excel表,匯入到資料庫中

--將所有的Excel檔案放到一個目錄中,假設為c:\test\,然後用下面的方法來做

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)

open tb

fetch next from tb into @fn

while @@fetch_status=0

begin

--下面是查詢語句,需要根據你的情況改為插入語句

--插入已有的表用:insert into 表 selct * from ...

--建立表用:select * into 表 from ...

set @fn="select * from

OPENROWSET(""MICROSOFT.JET.OLEDB.4.0"",""Excel 5.0;HDR=YES;DATABASE="+@fn+""",全部客戶$)"

exec(@fn)

fetch next from tb into @fn

end

close tb

deallocate tb

drop table #t