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.
From what I've read online, openrowset is read only so you need to use OPENDATESOURCE instead. Try something like this.