Inserting Binary Data

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

Jini JoseSenior .Net DeveloperCommented:
yes you can do that.
just loop through the databse records and get the filename
then find the file then save to database.
0
Matthew KellyCommented:
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
Matthew KellyCommented:
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
andyw27Author Commented:
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
Matthew KellyCommented:
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

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