Insert a file into a Varbinary(Max) field in MS SQL 2012

The easiest way in my opinion is to use the OPENROWSET(BULK). Scroll down the MSDN article at the link to the "Using OPENROWSET with the BULK Option" section. You will find a few options there:

SINGLE_BLOB
SINGLE_CLOB
SINGLE_NCLOB

All of them return the content of a file as a single-row, single-column rowset.
Imagine you have a table with a simple structure like this:

create table t (col1 varchar(max))

Select allOpen in new window

To insert a record into this table you will use this query:

insert into t
select * from openrowset(bulk N'c:\stats.txt', SINGLE_CLOB) as a

Select allOpen in new window

Now, if you have more columns in your destination table and want populate their values as well you need to use a slightly different query. Let's say your table structure is different:

create table t1 (id int, description varchar(100) ,content varchar(max))

Select allOpen in new window

the insert query will be:

insert into t1 (id, description, content)
select 100 as ID, 'new datafrom the vendors' as description, fileContent from openrowset(bulk N'c:\stats.txt', SINGLE_CLOB) as a(fileContent)

Select allOpen in new window

I.e. when you are creating the alias for the openRowset you need to provide the alias for the column as well (I have used a(fileContent))

Hi,
You can also copy the relevant file to Sql server folder and then do like this

update dbo.module_display
set photo_file=(SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\DATA\stats.txt', SINGLE_BLOB) AS SRC)
where mod_id=11;

Select allOpen in new window

I want to pull the name of the file from a text field only.

So if I have a 1000 records I need to go through each record and look in that text field and use the location of the file to insert the physical file into a varbinary(max) field

You can use a text field to the above script I showed. And use a loop to do for all records.

In this case you will need a script:

declare @txt varchar(max)
declare @line varchar(max)
declare @pos int
select @txt = fileContent from openrowset(bulk N'c:\stats.txt', SINGLE_NCLOB) as a(fileContent)
set @pos = charindex(char(10), @txt)
set @line = ''
if @pos > 0 
	set @line = left(@txt, @pos)
while @line <> ''
begin
	print @line
        insert into t1 (id, description, content)
        select 100 as ID, 'new datafrom the vendors' as description, fileContent from openrowset(bulk @line, SINGLE_CLOB) as a(fileContent)
	set @txt = stuff(@txt, 1, @pos+1, '')
	set @pos = charindex(char(10), @txt)
	set @line = ''
	if @pos > 0 
		set @line = left(@txt, @pos)
end

Select allOpen in new window

Dear Chauu,
I cannot put in the file name 'c:\stats.txt' because it is coming from a text field.


Dear  HuaMinChen,
Can you please give an example of how I would use a  text field in the script you showed?

Ok, I understand now. You have a table with a column that has a file name. You also have another varchar(max) column that is currently empty. It needs to be populated with the content of each file, something like that:

Id    filename    contents
1     c:\test1.txt 
2     c:\test2.txt

Select allOpen in new window

Am I right?

Yes please.

In this case you can simply use a query for this

update tbl2
set contents=(SELECT fileContents
FROM OPENROWSET(BULK tbl2.fileName, SINGLE_CLOB) AS a(fileContents));

Select allOpen in new window

In this case what is contents, fileName & fileContents ?  I assumed that contents was my varbinary(max) field and fileName was the field that contain the filename  & location but what is fileContents, assuming I am right about the rest?

FileContents is the alias for the column returned by the Openrowset command. Check my previous answer where I have explained it. Notice the last clause: AS a(fileContents)

Dear  HuaMinChen,
Can you please give an example of how I would use a  text field in the script you showed?

Try

declare @str
select @str=text_field_column from tab1 where ...

Select allOpen in new window

Dear Chaau,

update tbl2
set contents=(SELECT fileContents
FROM OPENROWSET(BULK tbl2.fileName, SINGLE_CLOB) AS a(fileContents));

When I tried this with my table and columns I am getting errors with the tbl2 part of  tbl2.fileName (Incorrect syntax near 'tbl2'. Expecting STRING, or TEXT_LEX).  I am also getting errors with the first fileContents (Invalid column name 'fileContents') and the last fileContents (Incorrect syntax near 'fileContents'. Expecting '(', or Select.)

Dear HuaMinChen,

Can you give the complete script again with the last piece you sent added in...I am still a bit confused.

Hi,
You need to quote the part within "OPENROWSET" like what I showed in above.

Dear HuaMinChen.

Is the script below what you are implying I should do?


declare @str
update dbo.module_display
set photo_file=(SELECT BulkColumn
FROM OPENROWSET(BULK select @str=text_field_column from tab1, SINGLE_BLOB) AS SRC)
where mod_id=11;

Hi,
Please ensure the relevant file does exist within the folder below, then you can have the following select and update, within a loop.

declare @str

select @str=text_field_column from tab1 where ...
update dbo.module_display
set photo_file=(SELECT BulkColumn
FROM OPENROWSET(BULK N'C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012\MSSQL\DATA\'+@str, SINGLE_BLOB) AS SRC)
where mod_id=11;

Select allOpen in new window

Dear HuaMinChen,

Is this script suppose to run at the query line or store procedure?

Please provide your table structure and some sample data

ID (int)   FileURL (varchar(50))     Data (varbinary(max))
       1                 c:\text.pdf                                    NULL
       2                 c:\data.pdf                                   NULL

Thanks