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

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

Open 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

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

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

Open 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))
0
HuaMin ChenProblem 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;

Open in new window

0
dpdmembersAuthor Commented:
I want to pull the name of the file from a text field only.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dpdmembersAuthor 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
HuaMin ChenProblem resolverCommented:
You can use a text field to the above script I showed. And use a loop to do for all records.
0
chaauCommented:
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

Open in new window

0
dpdmembersAuthor 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
chaauCommented:
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

Open in new window

Am I right?
0
dpdmembersAuthor Commented:
Yes please.
0
chaauCommented:
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));

Open in new window

0
dpdmembersAuthor 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
chaauCommented:
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
HuaMin ChenProblem 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 ...

Open in new window

0
dpdmembersAuthor 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
dpdmembersAuthor 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
HuaMin ChenProblem resolverCommented:
Hi,
You need to quote the part within "OPENROWSET" like what I showed in above.
0
dpdmembersAuthor 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
HuaMin ChenProblem 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;

Open in new window

0
dpdmembersAuthor Commented:
Dear HuaMinChen,

Is this script suppose to run at the query line or store procedure?
0
chaauCommented:
Please provide your table structure and some sample data
0
dpdmembersAuthor Commented:
ID (int)   FileURL (varchar(50))     Data (varbinary(max))
       1                 c:\text.pdf                                    NULL
       2                 c:\data.pdf                                   NULL
0
chaauCommented:
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

Open in new window

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.

Start your 7-day free trial
dpdmembersAuthor 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.