Exporting Copying Sending Sql Table data into Excel Using OpenRowSet/Sql Server
Introduction:
In this article,iam going to explain about how to export a sql table data into sql server using sql server 2008.
Main:
Before starting first we need to set the below configuration settings,
EXEC sp_configure 'show advanced options', 1;
GO RECONFIGURE;
GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO RECONFIGURE;
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; |
The config option EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1 allows you to run all open rowset queries,
INSERT INTO OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:Emp_Details.xls;',
'SELECT * FROM [Sheet1$]')SELECT * FROM EMP
For (.XLSX) format
INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=c:Emp_Details.xlsx;'
,'SELECT * FROM [Sheet1$]')SELECT * FROM EMP
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:Emp_Details.xls;', 'SELECT * FROM [Sheet1$]')SELECT * FROM EMP For (.XLSX) format INSERT INTO OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=c:Emp_Details.xlsx;' ,'SELECT * FROM [Sheet1$]')SELECT * FROM EMP |
Conclusion:
Hope this helps,
Happy Coding.