Exporting data to excel from sql server is not wor

2019-09-11 08:04发布

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条回答
女痞
2楼-- · 2019-09-11 08:28

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 
查看更多
登录 后发表回答