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.

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

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.

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

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

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

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

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

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

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

Filestream
Previous Post
SQL SERVER – Working with FileTables in SQL Server 2012 – Part 1 – Setting Up Environment
Next Post
SQLAuthority News – Presenting at Great Indian Developer Summit 2012 – SQL Server Misconception and Resolutions

Related Posts

7 Comments. Leave new

  • Hi Pinal,

    G.Moring I got it exact result.. Thank you sir.

    Reply
  • ChiragSatasiya
    April 16, 2012 2:02 pm

    Hi pinal Sir,
    Thank you for such new and fresh info about SQL Server.

    Regard$
    Chirag Satasiya

    Reply
  • passionateashu
    April 16, 2012 3:55 pm

    Thanks Sir

    Reply
  • myprogrammingexp
    April 16, 2012 5:12 pm

    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?

    Reply
  • Hi Pinal,

    Is it possible to have a join in DML statements(not as a part of sub query, )

    Reply
  • 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.

    Reply
  • Mahmut Haktan
    May 13, 2013 6:30 pm

    Hi,
    I want to insert may photo to FileTable from my table that consists image type field.
    Is that possible:

    insert into FileTable
    Select Picture from Photos

    Reply

Leave a Reply