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 (https://blog.sqlauthority.com)
7 Comments. Leave new
Hi Pinal,
G.Moring I got it exact result.. Thank you sir.
Hi pinal Sir,
Thank you for such new and fresh info about SQL Server.
Regard$
Chirag Satasiya
Thanks Sir
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?
Hi Pinal,
Is it possible to have a join in DML statements(not as a part of sub query, )
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.
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