INSERTING EXCEL DATA INTO SQL SERVER TABLE

Apr 2 2008 9:14 AM
Inserting Excel data into contains 3 methods:
1) Simple way is just copy all the data from excel and paste into the sql table(which is created         with simillar column names&datatypes).
    Then click on execute to save.
2) You can also query excel data using following query in sql server:
    SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source=D:\CountriesData.xls;Extended Properties=Excel 8.0')...Sheet1$
    Note: This can be done provided you configure your Sql Server as follows:
             sp_configure 'show advanced options', 1
             RECONFIGURE
             GO
             sp_configure 'Ad Hoc Distributed Queries', 1
             RECONFIGURE
             GO
3) By creating Linked Server:
      http://support.microsoft.com/kb/306397/EN-US/
     
Thank you. I'll welcome any suggestions or queries.


Answers (1)