Exporting data to excel from sql server is not wor

问题:

I am using the below code to export data to excel file. This is working fine when the excel file is blank. But when I update the filed to null then the insert query is executed successfully but the excel is showing blank.

update openrowset('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 
'select Column1,Column2,Column3 FROM [Sheet1$]')
set  Column1=null,Column2=null,Column3=null


insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=E:\..\.xlsx;', 'SELECT * FROM [Sheet1$]') 
select Column1,Column2,Column3 from table_Name 

I want set blank the file first before writing to that.

Kindly help.

回答1:

From what I've read online, openrowset is read only so you need to use OPENDATESOURCE instead. Try something like this.

UPDATE OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
    SET Column1=null,Column2=null,Column3=null


INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=E:\...\YourExcelFile.xlsx;;Extended Properties=Excel 12.0')...[Sheet1$]
   SELECT Column1,Column2,Column3 from table_Name