load excel file into temp SQL table

http://support.microsoft.com/kb/321686 got the detailed discussion of Excel import

If you are using SQL Management Studio, you can simply right click on a database, select Tasks, then select Import Data and have the Import Wizard walk you through importing your file - effectively it was create an SSIS package for you.

i just want to load the data into a temp table so i can work with it in a query,
eg
DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT YakID, YakName
FROM dbo.Yaks                   -----    -----------------------------------only here i need to point to excel
WHERE YakType = 'Tibetan'
 
 
 i could load it into an actual table, but its not want i want to be doing everytime  

hi catherine
i am using above to make

DECLARE @emails TABLE (email char(60))
INSERT INTO @emails(email)
SELECT email
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\mal\email.xls;Extended Properties=Excel 8.0')...[email$]
 
but i am getting error

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
 

I had the same error when the path to my file was incorrect.  Double check the path, that it is accessible, the filename and the sheet name.

the paths etc are all ok, reading the link to hopefully find something!

issue was i was connected to an sql instance on another server and reading a local file
i ran the query on the actual server and had the file saved locally and it worked 100%
cheers catherinelouise

I suspected it might be that - right at the end of the thread right? !  Glad you got it worked out.