SQL SERVER – Export Data From SQL Server to Microsoft Excel Datasheet

Question: How to Export Data From SQL Server to Microsoft Excel Datasheet?

Answer: 

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.

Solarwinds

SQL SERVER - Export Data From SQL Server to Microsoft Excel Datasheet dbtoexcel

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively, you can download sample Spreadsheet from here.

Run following code in SQL Server Management Studio – Query Editor.

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

Make sure your spreadsheet is closed during this operation. If it is open, it may throw an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

Let me know what you think of this blog post. It is very short but indeed a very effective query. I have been using it for a while and it works just fine.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – Download Frequently Asked Generic Interview Questions
Next Post
SQL SERVER – Introduction to Aggregate Functions

Related Posts

231 Comments. Leave new

  • mohamedtalaat2001a15
    October 7, 2015 7:29 pm

    how to delete date Before inser insert into OPENROWSET (‘Microsoft.ACE.OLEDB.12.0’,
    ‘Excel 8.0;Database=C:\Scale\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]’) select * from ScaleUpdate2

    Reply
  • Hi,
    how can I specify the start cell of the Excel file where to export data from SQL ?

    Reply
  • Eunhee… the best way to do this is to create a refreshable Excel report based on a SQL Server stored procedure.
    I do not know if Pinal wrote any articles on that. :(

    Reply
  • Thank You

    Reply
  • Hi, I have a problem with openrowset. I have:

    *SQLServer2012

    *WinServer2012R2 Standard

    *MicrosoftOffice2010 64

    *AcessDatabaseEngine 64

    I want run command:

    insert into OPENROWSET(‘Microsoft.Jet.OLEDB.12.0’, ‘Excel 12.0;Database=C:\testing.xls;’, ‘SELECT * FROM [Plan1$]’) select * from tabela

    and return the message:

    “the ole db provider “microsoft.jet.oledb.12.0″ has not been registered”

    What I need to do to fix this error? Help me Please!

    Reply

Leave a Reply

Menu