SQL Sever Read Write Excel Summary

The main use of the Openrowset,opendatasource system functions, either of these two functions can complete the task

This approach enables you to import and export between Excel and SQL Server tables.

If you use OPENROWSET error, you can refer to this article http://www.cnblogs.com/xiashengwang/p/3511808.html

The writing of OPENROWSET

 --3, link Excel Microsoft Jet 4.0 OLE DB Provider
--read Excel data, note that Excel must be closed in advance
Select*
from
OpenRowset('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;database=e:/temp/myexcel20131212.xls', sheet1$)

--writing data to a Excel2003 file
--Note: You first need to set some values that are equal to the retrieved columns for the first line of Excel's Sheet1, which can be any value,
--but the best is the same as the column name, and OLE DB may not have write permission to Excel, you need to add an everyone permission to the Excel table's properties-"security"
Insert into
OPENROWSET('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;database=e:/temp/myexcel20131212.xls', sheet1$)
Select* fromDbo. Dquestiondata

the writing of OpenDataSource

Select * 
from
OpenDataSource('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;database=e:/temp/myexcel20131212.xls')... sheet1$

The above code is a link Excel2003 , and for the Excel2007 link, the link will be different because OLE DB provider is different.

The following is a different way of linking, also found on the Internet.

 --> Jet engine access to Excel 97-2003
Select* fromOpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;imex=1;database=d:/97-2003.xls','select * FROM [sheet1$]')
Select* fromOpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;imex=1;database=d:/97-2003.xls',[sheet1$])
Select* fromOpenDataSource('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;imex=1;database=d:/97-2003.xls')...[sheet1$]
Select * fromOpenDataSource('microsoft.jet.oledb.4.0','Data Source=d:/97-2003.xls; Extended properties= "Excel 8.0; Hdr=yes;imex=1 "')...[sheet1$]

--> ACE engine access to Excel 97-2003
Select* fromOpenRowSet('microsoft.ace.oledb.12.0','Excel 12.0; Hdr=yes;imex=1;database=d:/97-2003.xls','select * FROM [sheet1$]')
Select* fromOpenRowSet('microsoft.ace.oledb.12.0','Excel 12.0; Hdr=yes;imex=1;database=d:/97-2003.xls',[sheet1$])
Select* fromOpenDataSource('microsoft.ace.oledb.12.0','Excel 12.0; Hdr=yes;imex=1;database=d:/97-2003.xls')...[sheet1$]
Select* fromOpenDataSource('microsoft.ace.oledb.12.0','Data Source=d:/97-2003.xls; Extended properties= "Excel 12.0; Hdr=yes;imex=1 "')...[sheet1$]

--> ACE engine access Excel
Select* fromOpenRowSet('microsoft.ace.oledb.12.0','Excel 12.0; Hdr=yes;imex=1;database=d:/2007.xlsx','select * FROM [sheet1$]')
Select* fromOpenRowSet('microsoft.ace.oledb.12.0','Excel 12.0; Hdr=yes;imex=1;database=d:/2007.xlsx',[sheet1$])
Select* fromOpenDataSource('microsoft.ace.oledb.12.0','Excel 12.0; Hdr=yes;imex=1;database=d:/2007.xlsx')...[sheet1$]
Select* fromOpenDataSource('microsoft.ace.oledb.12.0','Data source=d:/2007.xlsx; Extended properties= "Excel 12.0; Hdr=yes;imex=1 "')...[sheet1$]

SQL Sever Read Write Excel Summary