I'm trying to convert an Excel document into a table in SQL 2005. I found the link below and am wondering if it looks like a solution. If so, what would the @excel_full_file_name syntax be and where would the path be relative to?
You can use the BULK INSERT T-SQL command if you just want a pure sql solution. You have to save the file as csv/text first.
BULK INSERT YourDestinationTable FROM 'D:\YourFile.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO
Alternatively, you can try OPENROWEST - again , a pure T-SQL solution.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=D:\YourExcelFile.xls', 'Select * from YourExcelFile')
It really depends on how much control and flexibility you want, the SSIS route will have benefits over these methods.