Solved

Inserting Binary Data

Posted on 2010-08-18
5
976 Views
Last Modified: 2012-05-10
Hi,

I have a table that contains that contains the following columns

FileKey
FileName
FilePath

What I essentially would like to do is to add a fourth column that contains the binary data of the file.

Trouble is there are well over 500 files listed so I’m looking for help in creating a script that would do the update automatically?

Not sure if the best way is to use a scripting language (asp.net) to somehow loop through the records?

Any suggestions appreciated.
0
Comment
Question by:andyw27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 10

Expert Comment

by:Jini Jose
ID: 33469819
yes you can do that.
just loop through the databse records and get the filename
then find the file then save to database.
0
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 33470336
This should work (Using FETCH to store values in variables): http://msdn.microsoft.com/en-us/library/ms180152.aspx
DECLARE @FileKey int, @FileName varchar(50), @FilePath varchar(50);

DECLARE file_cursor CURSOR FOR
SELECT FileKey, FileName, FilePath FROM FileTable;

OPEN file_cursor;

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement. 

FETCH NEXT FROM file_cursor
   INTO @FileKey, @FileName, @FilePath;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

   -- Concatenate and display the current values in the variables.
   INSERT INTO FileTable (FileBinary) VALUES (LOAD_FILE(CONCAT(@FilePath, '\\', @FileName))) WHERE FileKey=@FileKey;

   -- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM file_cursor
   INTO @FileKey, @FileName, @FilePath;
END

CLOSE file_cursor;
DEALLOCATE file_cursor;
GO

Open in new window

0
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 33470377
Actually, LOAD_FILE is only in MySQL, so that insert would need to be replaced with the following.

See Example D on this page: http://msdn.microsoft.com/en-us/library/ms190312.aspx
INSERT INTO FileTable  (FileBinary)
SELECT *
FROM OPENROWSET (
BULK @FilePath + '\\' + @FileName,
SINGLE_BLOB) AS BinFile

Open in new window

0
 

Author Comment

by:andyw27
ID: 33473008
Thanks the suggestion.  I've given it a go, however I get a few errors on this bit:

 INSERT INTO FileTable  (FileBinary)
SELECT *
FROM OPENROWSET (
BULK @FilePath + '\\' + @FileName,
SINGLE_BLOB) AS BinFile

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@FilePath".

The individual error messages are:

Incorrect syntax near '@FilePath', Expecting STRING, or TEXT_LEX

Invalid Object name 'SINGLE BLOB'

(My FileBinary field is of type varbinary(MAX))
0
 
LVL 18

Accepted Solution

by:
Matthew Kelly earned 500 total points
ID: 33479924
http://www.sqlservercentral.com/Forums/Topic469199-5-1.aspx

Sorry, OPENROWSET doesn't take a variable, so it must be done with EXEC, creating dynamic SQL
Set @sql = '
             INSERT INTO FileTable  (FileBinary)
SELECT *
FROM OPENROWSET (
BULK ''' + @FilePath + '\\' + @FileName + ''',
SINGLE_BLOB) AS BinFile            
'
        Exec (@sql)

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question