How to create table with filestream column and Insert data

How to create table with filestream column and Insert data?

Filestream data type in SQL Server can be used to store images,documents,etc., in database.

In this blog I will explain how to create table with filestream data type.

Before creating table with filestream column,we have to enable filestream feature in SQL Server configuration manager.Follow “How to enable and configure Filestream in SQL SERVER 2008 / 2012”

Create a table and adding filestream data:

1.Create Filestream enabled database.

Syntax:

CREATE DATABASE test
ON
PRIMARY ( NAME = test1,
    FILENAME = ‘c:\data\testdat1.mdf’),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = test3,
    FILENAME = ‘c:\data\test1’)
LOG ON ( NAME = testlog1,
    FILENAME = ‘c:\data\test1.ldf’)
GO

2.After you have a filestream enabled database.we now need to create a table and add data to it.

Syntax:

CREATE TABLE [dbo].[test](
   [ID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
   [Number] VARCHAR(20),
   [Description] VARCHAR(50),
   [Image] VARBINARY(MAX) FILESTREAM NULL
)

3.Insert values into table. In below example I am inserting image file in to the table.

Syntax:

DECLARE @img AS VARBINARY(MAX)
— Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(Bulk ‘C:\image\jellyfish.jpg’, SINGLE_BLOB ) AS y
— Insert the data to the table
INSERT INTO test (ID,Number,Description, Image)
SELECT NEWID(), ‘MS1001′,’jellyfish’, @img