Microsoft - SQL Server - Snippets - Write to Excel file

Tested with the Microsoft Access Database Engine 2016 Redistributable with SQL Server 2016 on Windows Server 2016. Have not yet tested this from a SQL Server Agent job, which was the intention.

Prerequisites:

  1. The Excel file should already exist.
  2. The Excel file should already contain the exact number of required columns.

Microsoft Access Database Engine Redistributables:

Steps:

  1. Install the Microsoft Access Database Engine 2016 Redistributable.
  2. Enabling Ad Hoc Distributed Queries is required:
    sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO
  3. Use the following query:
    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=NO;IMEX=1;Database=C:\Map1.xlsx;','SELECT * FROM [Sheet1$]')
SELECT * FROM sysobjects
    1. HDR=NO: the first row does not contain column headers.
    2. IMEX=1: "a safer way to retrieve data for mixed data columns"
    3. Both HDR and IMEX are not mandatory parameters.

Sources: