.::: Export MSSQL SQL Server to csv using OPENROWSET :::.

 

https://teguhth.blogspot.com/2025/12/backup-restore-table-sql-server-using.html

https://teguhth.blogspot.com/2026/04/sample-lab-using-openrowset-in.html

1. create excel dummy with header save as "openrowsori.xlsx"
 

2. replace openrows.xlsx (

copy openrowsori.xlsx openrows.xlsx

Karena Excel (via Microsoft ACE OLEDB):

tidak support DELETE
tidak support TRUNCATE
tidak support OVERWRITE 

3. write to csv 

INSERT INTO OPENROWSET(
    'Microsoft.ACE.OLEDB.16.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\bcp\openrows.xlsx',
    'SELECT * FROM [Sheet1$]'
)
SELECT *
FROM teguhth.dbo.pembelian;

INSERT INTO OPENROWSET(
    'Microsoft.ACE.OLEDB.16.0',
    'Excel 12.0;HDR=YES;Database=C:\bcp\openrows.xlsx',
    'SELECT * FROM [Sheet1$]'
)
SELECT *
FROM teguhth.dbo.pembelian;
 

4. Result