CREATE TABLE files

(

--FileId INT IDENTITY(1,1),

filepath varchar(512)

)

--I had my IMAGE file path in out.txt

BULK INSERT dbo.files

FROM 'D:\SQL\SQLWorkOut\out.txt'

WITH

(BATCHSIZE = 1,

ROWTERMINATOR =''

);

select * from files

CREATE TABLE bindata

(data varbinary(max),

fileid INT IDENTITY(1,1)

)

/*

CREATE NONCLUSTERED INDEX file_id_idx ON bindata (fileid)

*/

DECLARE @filename VARCHAR(512),@sql VARCHAR(512);

DECLARE a_cur CURSOR STATIC FOR

SELECT filepath FROM files;

/*WHERE file_id >(SELECT MAX(file_id) FROM bindata);

This part is needed when you load the data NOT for the first time from the [files] table. */

OPEN a_cur

FETCH NEXT FROM a_cur INTO @filename;

WHILE (@@fetch_status=0)BEGIN

--using dynamic sql in order to use the @filename variable

SET @sql =' INSERT INTO bindata(data)

SELECT * FROM

OPENROWSET(BULK '''+ @filename+''', SINGLE_BLOB) as f;'

EXEC (@sql);

FETCH NEXT FROM a_cur INTO @filename;

END;

CLOSE a_cur;

DEALLOCATE a_cur;

SET NOCOUNT ON

DECLARE @a INT

DECLARE @i INT,

@filename VARCHAR(300),

@cmd VARCHAR(500)

SET @i = 0;

SELECT @a=max(fileid) from bindata; -- for speeding up this queryabit you may want to create and INDEX on file_id

WHILE @i != @a

BEGIN

SET @filename = 'D:\Temp\poto\'+CAST (@i AS VARCHAR)+'.jpg';

--SELECT @filename;

SET @cmd =

'bcp "SELECT data FROM test.dbo.bindata WHERE fileid='+ CAST(@i+1 AS VARCHAR)+'" queryout '+@filename+' -T -N';

EXEC master..xp_cmdshell @cmd;

SET @i=@i+1;

END;