How to convert query results into excel file and store the file as a varbinary in table without accessing any local path in SQL

See more:

SQL

SQL-Server

SQL-Server-2008R2

, +

I want to convert query results into excel file and store that file as a varbinary in a table. It can be done by accessing local path like below.

But, is it possible without exporting the file into local path ?

What I have tried:

DECLARE @cmd VARCHAR(8000), @export_directory VARCHAR(300) = 'D:\', @filename VARCHAR(300) = ''

CREATE TABLE ##temp_data(value VARCHAR(100))
INSERT ##temp_data
SELECT 'TEST'

SELECT @filename = 'excelfile.csv'
SELECT @cmd = 'bcp "SELECT * FROM ##temp_data " queryout "#OUTDIR#\#OUTFILE#" -q -S "#SERVER#" -c -t "," -T'

SET @cmd = REPLACE(@cmd, '#DB#', DB_NAME());
SET @cmd = REPLACE(@cmd, '#OUTDIR#', @export_directory);
SET @cmd = REPLACE(@cmd, '#OUTFILE#', @filename);
SET @cmd = REPLACE(@cmd, '#SERVER#', @@SERVERNAME);

EXECUTE master..xp_cmdshell @cmd,no_output
GO

-----------------------------
CREATE TABLE ##VarbinaryExample (id int, DocData VARBINARY(MAX))

INSERT INTO ##VarbinaryExample
SELECT 1, bulkcolumn
FROM OPENROWSET( BULK 'D:\excelfile.csv', SINGLE_BLOB ) AS y
-----------------------------
GO



SELECT*FROM ##VarbinaryExample
GO