My task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7. I appreciate the help.
DECLARE @i int
SET @i = 951
WHILE (@i <= 951)
BEGIN
DECLARE @SQL varchar(MAX)
SELECT @SQL =
'INSERT INTO images (image_name, image_data)
SELECT
' + convert(nvarchar(5), @i) + ' AS image_name,
BulkColumn FROM OpenRowSet ( Bulk ''C:\DB\' +
convert(nvarchar(5), @i) +
'.jpg'', Single_Blob) AS image_data'
EXEC (@SQL)
SET @i = @i + 1
END
GO
No comments:
Post a Comment