SQL SERVER – Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table

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)

10 thoughts on “SQL SERVER – Working with FileTables in SQL Server 2012 – Part 2 – Methods to Insert Data Into Table

  1. This is something I was not aware of. If copying a file to the folder updates the record in the filetable, how can we implement auditing?

    Also is it possible for a user to insert records in the filetable, but not have direct access to the folder?

    Like

  2. Pingback: SQL SERVER – Working with FileTables in SQL Server 2012 – Part 3 – Retrieving Various FileTable Properties « SQL Server Journey with SQL Authority

  3. Hello Sir,

    can you show us how to create stored procedure for INSERT data in FileTable. According to your example i try to make stored procedure with parameters to INSERT data but i have error. Thanks.

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #025 | SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

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