I have got huge list of contact information in an Excel sheet that I would like to turn into table in the database so that I can maintain them in the database.
I tried following the import/export flat file import from the SQL Server 2008 R2, but it did not work. Can anyone tell me how do I successfully import the spreadsheet into a table in the database?
Thanks
There is a microsoft knowledge base article that lays out all the ways this is possible.
http://support.microsoft.com/kb/321686
I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
See OPENROWSET documentation, with examples lower down the page.
http://msdn.microsoft.com/en-us/library/ms190312.aspx
Manually
Right click on the database name/go to task and then select import data, as a source select an excel file that you created before and choose it's path on the next page select sql server as destination