Insert a client file into a column on a server database

Question

I wish to write a query that inserts a file that resides on the client (C# web server) into a column in the database server (SQL Server), something like INSERT … SELECT * FROM OPENROWSET(BULK…), but without having to save the file on the server machine first.

Is this even possible in SQL?

Questions normally involve queries, interrogatives, and/or question marks. As written, an accurate "answer" to your "question" could be **Neat, have fun!** — JNK, Sep 19 '11 at 17:33
Yes its possible, but the implementation might vary depending on the client software. For example this might be different depending on if you're using SSIS or a Web App. Also what's the datatype of the column e.g BLOB, NTEXT, FileStream? — Conrad Frix, Sep 19 '11 at 19:50
The client is C# Web App, the data type of the column is what ever I choose that will work :) Will you please elaborate on how it is possible? — kroiz, Sep 20 '11 at 06:11

score 2 · Answer 1 · edited Jan 28 '12 at 20:00

Although your context is unstated, I'm assuming that you're intending to run this from SSMS rather than from OSQL, a PowerShell script, or through some other means.

The file doesn't need to reside on the physical box running SQL Server, but SQL Server does need access to it. The typical approach, I believe, would be for an application server to copy the file to a shared repository and then pass it off to SQL Server through a UNC reference. The syntax to do so is relatively trivial and can be found in Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).

If instead you're interested in providing a mechanism for the SQL Server to save a file from some type of stream operation where the client is directly transmitting a file and there is no shared repository, I'm not aware of a way to do that. Even if you use an SQL FILESTREAM object you still need an accessible NTFS location to stream from. See Saving and Retrieving File Using FileStream SQL Server 2008.

+1 I also removed my prior comments. They just add noise at this point but then again so does this — Conrad Frix, Sep 20 '11 at 05:30

score 1 · Answer 2 · edited May 23 '17 at 11:47

At some point, the server will have to have a hand on the file. That does not mean that the server has to keep the file, but the file has to get to the server in order to be read and inserted into the db. Typically, this is achieved with a form and a file-type input. On the server, you can use the uploaded file to create your query, then delete it.

That said, storing files in a database is a debatable practice. Depending on the type and size of files you're storing, your database can quickly balloon in size. For starters, this makes backups slower and more prone to failure, along with a laundry list of other potential pitfalls. Check out this question on SO: Storing Images in DB - Yea or Nay? As you can see from the answers, there are a number of considerations to be made, but a good rule of thumb is to not do this unless you have compelling reasons to do so.

score 0 · Accepted Answer · edited Jan 28 '12 at 20:02

In SQL Server BLOB Data in .NET: Tutorial, Mohammad Elsheimy explains how it can be done:

using(SqlConnection con = new SqlConnection(conStr) )
using(SqlCommand command = new SqlCommand("INSERT INTO MyFiles VALUES (@Filename, @Data)", con) )
{
    command.Parameters.AddWithValue("@Filename", Path.GetFileName(filename));
    command.Parameters.AddWithValue("@Data", File.ReadAllBytes(filename));
    connection.Open();
}

Basically, this way the file is read on the client and sent to the database server without a need for a temporary file on the server machine.

Insert a client file into a column on a server database

3 Answers3