How to insert data or a file that exists in the drive into a file stream table using T-SQL?

Problem: How to insert data or a file that exists in the drive into a file stream table using T-SQL?

Solution: OPENROWSET Starting SQL Server 2008

OPENROWSET is a function which supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a row set.

To know more about how to create a file stream on a database. Please go through this article. Create File Stream on a Database

To know more about how to create a file stream table. Please go through this article. Create File Stream Table

Example:

In this Example, I would like to show we can insert a test file into the file stream enabled database and retrieve to see the file is saved in the data file directory where file stream data is saved.

Input file selected to insert as the below screen shot.

Insert statement:

INSERT INTO [dbo].[DocumentFilestream] (

                DocumentID,

                Content,

                FileName,

                FileType,

                FileSize

                                )

VALUES (

                123,

                (SELECT * FROM OPENROWSET(BULK N'C:\Temp\test.sql', SINGLE_BLOB) ),

                 'Test',

                  'sql',

                  2

                );

GO

OUTPUT:

 as we can see the file has been saved into the file stream folder and the filename is encrypted and when we open the file with notepad it still has the same query. In the case of an image the data will stored in binary format.