Sunday, March 8, 2015

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

Search This Blog