• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Import Excel in SQL server

Hi,
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',
    'Excel 8.0;Database=D:\testing.xls;HDR=YES',
    '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
0
esotericmee
Asked:
esotericmee
  • 5
  • 3
1 Solution
 
sachitjainCommented:
Did you try SQL Server's import and export wizard?
0
 
esotericmeeAuthor Commented:
No, I am interested in using T-SQL only. I dont want to use SSIS or import export wizard
0
 
sachitjainCommented:
Start doing it through import export wizard, in one of steps it would reveal you the underlying query that it would be executing to accomplish the load. There you could simply run away with that query without completing the execution through wizard. :-)
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
esotericmeeAuthor Commented:
I tried to use the wizard but I didnt find the code in the process anywhere. Thanks.
0
 
esotericmeeAuthor Commented:
Bulk Insert works fine with CSV and I have used it for CSV's in the past, I havent been able to use it for Excel though.
0
 
sachitjainCommented:
Sorry for unrealistic suggestion. I was under impression that it some stage it revealed the query to be executed. Try this out.
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/33436d82-085c-43e4-b991-a2d0d701c8fc
0
 
esotericmeeAuthor Commented:
I have solved this question . I ended up using SSIS and calling it thru my proc.
0
 
esotericmeeAuthor Commented:
I did not get any acceptable solution.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now