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

I have the location of files (e.g. c:\stats.txt) in a text field and I want to insert/Update records with the physical file using that field's location  into a Varbinary(Max) field.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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))

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

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))

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)

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))
0
Problem resolverCommented:
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;

0
Author Commented:
I want to pull the name of the file from a text field only.
0
Author Commented:
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
0
Problem resolverCommented:
You can use a text field to the above script I showed. And use a loop to do for all records.
0
Commented:
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

0
Author Commented:
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?
0
Commented:
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

Am I right?
0
Author Commented:
0
Commented:
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));

0
Author Commented:
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?
0
Commented:
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)
0
Problem resolverCommented:

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 ...

0
Author Commented:
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.)
0
Author Commented:
Dear HuaMinChen,

Can you give the complete script again with the last piece you sent added in...I am still a bit confused.
0
Problem resolverCommented:
Hi,
You need to quote the part within "OPENROWSET" like what I showed in above.
0
Author Commented:
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;
0
Problem resolverCommented:
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;

0
Author Commented:
Dear HuaMinChen,

Is this script suppose to run at the query line or store procedure?
0
Commented:
Please provide your table structure and some sample data
0
Author Commented:
ID (int)   FileURL (varchar(50))     Data (varbinary(max))
1                 c:\text.pdf                                    NULL
2                 c:\data.pdf                                   NULL
0
Commented:
You will need to use a cursor, like this:
--create table tbl2 (
--id int,
--fileURL varchar(50),
--Data varbinary(max))
--insert into tbl2(id, fileurl) values(1, 'd:\temp\test1.doc');
--insert into tbl2(id, fileurl) values(2, 'd:\temp\test2.doc')

DECLARE @sql NVARCHAR(MAX)
DECLARE @id int
, @fileURL varchar(50)
DECLARE c CURSOR for SELECT id, fileurl FROM tbl2
OPEN C
FETCH NEXT FROM C INTO @id, @fileURL
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'update tbl2
set Data=(SELECT fileContents
FROM OPENROWSET(BULK N''' + @fileURL + ''', SINGLE_BLOB) AS a(fileContents))
WHERE id = ' + CAST(@id AS varchar(20))

EXEC sp_executesql @stmt = @sql
FETCH NEXT FROM C INTO @id, @fileURL
END
CLOSE c
deallocate c

select * from tbl2

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.