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

clip_image001[8]

4.To view the table with filestream included column

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s