Read Part 1 Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment

In this second part of the series, we will see how we can insert the files into the FileTables. There are two methods to insert the data into FileTables:

Method 1: Copy Paste data into the FileTables folder

First, find the folder where FileTable will be storing the files. Go to Databases >> Newly Created Database (FileTableDB) >> Expand Tables. Here you will see a new folder which says “FileTables”. When expanded, it gives the name of the newly created “FileTableTb”. Right click on the newly created table, and click on “Explore FileTable Directory”. This will open up the folder where the FileTable data will be stored.

When clicked on the option it will open up following folder in my local machine where the FileTable data will be stored.

\\127.0.0.1\mssqlserver\FileTableDB\FileTableTb_Dir

You can just copy your document just there. I copied few word document there and ran select statement to see the result.

USE [FileTableDB]
GO
SELECT *
FROM FileTableTb
GO

SELECT * returns all the rows. Here is SELECT statement which has only few columns selected from FileTable.

SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO

I believe this is the simplest method to populate FileTable, because you just have to move the files to the specific table.

Method 2: T-SQL Insert Statement

There are always cases when you might want to programmatically insert the images into SQL Server File table. Here is a quick method which you can use to insert the data in the file table. I have inserted a very small text file using T-SQL, and later on, reading that using SELECT statement demonstrated in method 1 above.

INSERT INTO [dbo].[FileTableTb]
([name],[file_stream])
SELECT
'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB) AS FileData
GO

The above T-SQL statement will copy the NewFile.txt to new location. When you run SELECT statement, it will retrieve the file and list in the resultset. Additionally, it returns the content in the SELECT statement as well. I think it is a pretty interesting way to insert the data into the FileTable.

SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].[FileTableTb]
GO

There are more to FileTable and we will see those in my future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Filestream