Is there a way to import data in a table in SQL server 2005/08 from Excel. This can be easily done with SSIS but I want to implement it using T-SQL. Something similar to BULK INSERT for csv files.
I have tried this
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'SELECT * FROM [SheetName$]')
but it failed with the error "OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Is there a 32/64 bit issue here?
Any other simpler solution?
Thanks for your help