How To Store Any File into SQL Database

Hello friends, I am posting a solution to store any file in SQL server table and use it any where in an application as downloadable file

using OPENROWSET you can insert a file data in binary format to SQL table

Lets take one example to understand completely

One simple scenario is to just store the file in table and read from it from an application/ website in WCF or .NET

Create one table with a column as varbinay type

CREATE TABLE [dbo].[FileTable](
 [FileName] [nvarchar](50) NOT NULL,
 [FileData] [varbinary](max) NOT NULL
) 
GO

Using above query table will be created

Now to store file in this table use below SQL query

DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
INSERT INTO [JPDB].[dbo].[FileTable]
VALUES('PDFFile.pdf',@Data)

Above SQL Query will store binary data of file: 'D:\JP\Bloger\PDFFile.pdf' in the column 'FileData' of table 'FileTable'

Now if we execute below query

SELECT [FileName]
      ,[FileData]
  FROM [JPDB].[dbo].[FileTable]

We will get the result

This table can be used in any application to download this stored file easily

For example in C# you can use Response.BinaryWrite to download the file from SQL DB

I would like to explain one more thing here as per my experience in IT industry, many projects does not allow varbinary cloumn data type in a table

In these type of cases, we can insert data existing schema of database in varchar type column

To insert file data in a varchar type column we can use same approach as explained above

Suppose we have one table with following schema

To insert file data in this table having column varchar type, we need two steps:

First insert binary data in a temporary Table in varbinary column using below query

DECLARE @Data varbinary(MAX) = (SELECT * FROM OPENROWSET(BULK N'D:\JP\Bloger\PDFFile.pdf',SINGLE_BLOB) AS AnyFile);
SELECT 'PDFFile.pdf' AS [FileName],@Data AS [FileData]
INTO [JPDB].[dbo].[temporaryTable] 

SELECT [FileName]
      ,[FileData]
  FROM [JPDB].[dbo].[temporaryTable]

Above query will insert file data in table 'temporaryTable'

Now in second step copy this binary data of file in the table which has varchar column data type

Best way to insert binary data (actually this is hexadecimal string) in varchar type column is, generate sql script from temporaryTable

Follow below steps to generate script of table with data. Also you can refer my blog "How to generate Insert Script of a table in SQL Server"

Right click on DB -> Task -> GenerateScripts

A new Window will open as shown below, then click on 'Next' button

After clicking on Next button, a new wizard will open for selecting object for which you want to create script, select second radio button for selecting specific database object and then select the check-box of your table as shown below

After clicking on 'Next' button, Click on 'Advance' button

In the advance option change general settings of 'Types of data to script' -> Schema and data and then click on Ok button

After clicking on Ok button, you can change directory path where you want to store your script and then click on 'Next' button

Click on 'Next' button

Click finish button, your script will be stored in the given location

Open the script in SQL Server

Copy the hex string as highlighted in the above image to insert in the table which has column of varchar type, as shown in below query

SELECT TOP 1000 [FileName]
      ,[FileData]
  FROM [JPDB].[dbo].[FileTable2]

Now table 'FileTable2' has PDF file data in hexadecimal string, we can convert hexadecimal string into Byte to read this file in C# or any other language

SQL Server 2012 provide better functionality to store files in DB with File Table, to see more detail refer msdn

How to fetch last row from a table Or How to fetch Second Last row from a SQL table

This is similar to my blog-post: How to fetch second row from a table in SQL Server
To fetch rows from last, first you have to query number of rows in your table then use this number in OFFSET FETCH clause
Consider I have a table with following Data

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]

Result:

Below is the query to fetch Last row in a result set

DECLARE @Total [int]

SET @Total= (SELECT COUNT(*) FROM [JPDB].[dbo].[Employee])
SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]
  ORDER BY [ID] ASC OFFSET (@Total-1) ROWS FETCH NEXT 1 ROWS ONLY

Result:

I am taking one more example, suppose I want last three rows
Below is the query

DECLARE @Total [int]

SET @Total= (SELECT COUNT(*) FROM [JPDB].[dbo].[Employee])
SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]
  ORDER BY [ID] ASC OFFSET (@Total-3) ROWS FETCH NEXT 3 ROWS ONLY

Result:

One more trick to get last row from a table using OFFSET FETCH Clause
Use reverse order DESC in place of ASC
Below is the query

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]
  ORDER BY [ID] DESC  OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

Result

How to fetch second row from a table in SQL Server, Or How to retrieve specific row from a ordered result of SQL Query

SQL Server 2012 has many enhanced features, one is OFFSET FETCH clause. To fetch second row or any specific row from an ordered result of SQL query we can use this feature of SQL Server. We can fetch specific row from ordered result set using only single statement as I explained below.

OFFSET FETCH Clause

The OFFSET-FETCH clause provides us option to fetch only a window or page of results from the result set. The only limitation is, you can use this with ORDER BY clause only. That make sense actually, if we want second row of a result set it means some order should present from which we want second row
I have one table which has employee information as shown below

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]

Result of above query is:

Below is the SQL script using OFFSET FETCH to get 2nd row of a result

 SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]
  ORDER BY [ID] ASC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

The number I have written after OFFSET skips rows, and the number written after NEXT get total number of rows
Result of above query:

Suppose I want 2nd and 3rd row then below is the query:

SELECT [ID]
      ,[Name]
      ,[Post]
  FROM [JPDB].[dbo].[Employee]
  ORDER BY [ID] ASC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY

Result: