SQL Server Portal

In my previous post, I discussed how we could apply different Data Definition Language (DDL) Statements on File Table. Today, we will discuss how to apply Data Manipulation Language (DML) Statements on a File Table.

• Insert record in File Table :

There are two ways to do it.

1. Via SQL Server Management Studio (GUI)
2. Via Transact SQL

1-Via SQL Server Management Studio (GUI)
This is the simplest way of insertion of any record in the File Table.
Lets do it step by step :

Step 1:
First copy the file that you want to place in the file table. For example abc.txt (text file).

Step 2:
Then go to the database, where you have created a file table and then select the file table and right click on it.

Step 3:
Click on the Explore File Table Directory

Step 4:
It will open the directory pointing to this file table. Just paste the file (abc.txt) in it.

Step 5:
After that just execute the select statement to verify whether the file is there or not.

Use MyFileTableDB
GO
select * from databank

2-Via Transact SQL

Given below is the method to insert data into file table. But make sure the file is more than 0 KB.

INSERT INTO [dbo].[databank] ([name],[file_stream])
SELECT'Test file',
* FROM OPENROWSET(BULK N'C:\abc-2.txt', SINGLE_BLOB) AS FileData
GO

• Select records from File Table :

The select statement of a file table is same like a normal table. Given below is the script to browse file table :

Select * from dbo.DataBank

• Update records in File Table :

Given below is the script to update file table.

Update Databank set [name]='test file-2' where [name]='test file'

Once you update the file table and change the name of the file, execute the select statement to verify whether the update is successfully done or not.

Select * from dbo.DataBank

• Delete records in File Table :

Given below is the script to delete files from the file table. Make sure you select the correct file name, because it will not only delete record from the file table but also physically delete the file from the directory as well.

Delete from DataBank where [name]='test file-2'

Once you delete the file from file table, execute the select statement to verify whether the delete is successfully done or not.

Select * from dbo.DataBank

Read Full Post »