How to Import/ Insert Picture into Database using SQL Query

Here is simple script for importing picture into SQL database. You don't need to write a program for importing picture. just follow the below procedure and insert bulk image into database.

(1) Declare a variable with any name and copy the image path into variable.

(2) Use the cursor to fetch the list of images.

(3) Get ID from table and insert picture against that ID.

DECLARE @Path AS VARCHAR(2000)

SET @Path ='\\192.168.1.1\d\IMG\'

CREATE TABLE #ImageFile (

       id int IDENTITY(1,1)

      ,subdirectory nvarchar(512)

      ,depth int

      ,isfile bit);

INSERT  #ImageFile(subdirectory,depth,isfile)

EXEC master.sys.xp_dirtree @Path ,0,1;

select * from #ImageFile

--drop table #ImageFile

DECLARE @FileName varchar(100)

DECLARE @sql_string nvarchar(max) 

Declare @PersonID varchar(50)

DECLARE @PersonImage CURSOR

SET @PersonImage = CURSOR FOR SELECT subdirectory FROM #ImageFile

OPEN @PersonImage

FETCH NEXT FROM @PersonImage INTO @FileName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @PersonID=Replace(@FileName, '.jpg', '')

SET @sql_string=N'UPDATE CUSTOMER SET CUST_Image = (SELECT MyImage.* FROM Openrowset(Bulk ' + quotename(@Path + @FileName,nchar(39)) + ', Single_Blob) MyImage) where CUSTOMER_ID = ''' + @PersonID + '''';

print @sql_string

EXECUTE sp_executesql @sql_string

FETCH NEXT FROM @PersonImage INTO @FileName

END

CLOSE @PersonImage

DEALLOCATE @PersonImage

drop table #ImageFile

GO