How to Insert Data into a FILESTREAM Table in SQL Server

We have created a FILESTREAM table using the following code:

CREATE TABLE [dbo].[FS_Table]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[UI] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
[FS_Data] [varbinary](max) FILESTREAM NULL
)

To insert data into a FILESTREAM Table use the following T-SQL code (it will insert image_001.jpg file from D:\temp\ folder to the dbo.FS_Table in the FS_Database):

Use FS_Database
GO
INSERT INTO [dbo].[FS_Table] (Id, UI, FS_Data)
VALUES (1
,NEWID()
,(SELECT * FROM OPENROWSET(BULK N'D:\temp\image_001.jpg', SINGLE_BLOB) AS Image001)
);
GO

Insert_FILESTREAM_Data

You can see the data is successfully inserted and exists in the table:

SELECT Id, UI, FS_Data
FROM [dbo].[FS_Table]

Select_FILESTREAM_Data

New folder will be created in D:\Data\FileStreamData that was defined as the path for the FILESTREAM filegroup, and the inserted image is located there:

FILESTREAM_Data_location

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">