How to Insert Filestream data

Question

How to Insert Filestream data

luckysql.kinda

SSCertifiable

Points: 7816
More actions
February 16, 2010 at 6:39 am

#219211

Hi all,
I have used following commands to make my DB capable of tackling Filestream.
ALTER DATABASE ThisTest ADD
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM;
GO
ALTER DATABASE ThisTest ADD FILE (
NAME = FSGroup1File,
FILENAME = 'D:\sql\FSDATA')
TO FILEGROUP FileStreamGroup1;
GO
USE ThisTest;
GO
CREATE TABLE DocumentStore (
DocumentID INT IDENTITY PRIMARY KEY,
Document VARBINARY (MAX) FILESTREAM NULL,
DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
UNIQUE DEFAULT NEWID ())
FILESTREAM_ON FileStreamGroup1;
GO
But now how should I insert the data in this table? I have five pics (each 600 KB).

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Low Rider SSCommitted Points: 1502 More actions · Answer 1

Can someone please explain how i can add a bob object like a word document etc in the sql server 2008 db.

I am not able to understand how exactly a file etc can be saved in the db. If for eg i have a file stored on my local sql server C:\Myfolder\Myfile.doc how exactly can i save this in the db using managment studio tsql query.

thanks in advance.

Håvard Mr or Mrs. 500 Points: 569 More actions · Answer 2

Hi,

Take a look at this link:

http://weblogs.asp.net/aghausman/archive/2009/03/16/saving-and-retrieving-file-using-filestream-sql-server-2008.aspx

It's a brilliant explanation on how to implement a simple filestream application. I created a WPF application, instead of ASP.NET. But I found the author's descriptions on how to read and write FileStream data highly useful!

I hope this point you in the right direction:-)

Low Rider SSCommitted Points: 1502 More actions · Answer 3

Thanks for the link but it explains using ado.net, is there a way i can insert data using the managment studio.

I really do not use ado.net

thanks for your time

Håvard Mr or Mrs. 500 Points: 569 More actions · Answer 4

I'm really not an expert on this, but shouldn't it in some way be possible to use the code in the stored procedure directly?

That is, create/adapt your own T-SQL script based on the code from the link.

I haven't tried it myself, but with a little time and adaptation I think you should get it to work.

Good luck!

JasonYousef SSCommitted Points: 1769 More actions · Answer 5

Well you could use this article, but I don't think that's what you need

http://www.mssqltips.com/sqlservertip/1489/using-filestream-to-store-blobs-in-the-ntfs-file-system-in-sql-server-2008/

or use a simple query using "OPENROWSET"

assume that you have a a table with 2 columns..

CREATE TABLE [dbo].[Images](

[ID] uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,

[Image] [varbinary](max) filestream default NULL

) ON [PRIMARY]

you would insert using...

INSERT INTO [FStream].[dbo].[Images]

([ID],[Image])

VALUES

(NEWID(),

(SELECT * FROM OPENROWSET

(BULK 'C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\1.jpg',

SINGLE_BLOB) AS [Image]))