Welcome to the second part of our series on working with FileTables in SQL Server 2012. In this article, we will explore how to insert files into FileTables using two different methods.
Method 1: Copy and Paste
The simplest way to populate a FileTable is by copying and pasting files directly into the FileTable folder. To do this, follow these steps:
- Find the folder where the FileTable will be storing the files. This can be done by navigating to Databases > Newly Created Database (FileTableDB) > Expand Tables.
- Locate the folder named “FileTables” and expand it to find the newly created FileTable.
- Right-click on the FileTable and select “Explore FileTable Directory”. This will open the folder where the FileTable data will be stored.
- Copy and paste your files into this folder.
Once the files are copied, you can run a SELECT statement to view the inserted data. For example:
USE [FileTableDB]
GO
SELECT * FROM FileTableTb
GO
This SELECT statement will return all the rows in the FileTable. You can also customize the columns to be selected in the query.
Method 2: T-SQL Insert Statement
If you prefer to programmatically insert files into the FileTable, you can use a T-SQL INSERT statement. Here’s an example:
INSERT INTO [dbo].[FileTableTb] ([name], [file_stream])
SELECT 'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB) AS FileData
GO
In this example, we are inserting a small text file called “NewFile.txt” into the FileTable. The file is read using the OPENROWSET function and then inserted into the FileTable. You can then retrieve the file and its content using a SELECT statement, similar to the previous method.
These two methods provide different ways to insert files into FileTables in SQL Server. Depending on your requirements, you can choose the method that suits your needs.
Stay tuned for future blog posts where we will explore more features and concepts related to FileTables in SQL Server.