T-SQL listing given below  helped me to resolve the issue. Note that image folder should be accessible from the SQL server. If the image folder is  in the remote location, either you have to copy to the server or map the folder in SSMS
--Target Table where data to be imported
CREATE TABLE userinfo
  (
     USER_ID      INT NOT NULL IDENTITY(1, 1),
     logonname    VARCHAR(200),
     profileimage VARBINARY(MAX)
  ) GO --Table to process the filesCREATE TABLE imagelist
  (
     imgfilename VARCHAR(200)
  ) GO --Put all file name in a table for easy processing
DECLARE @SQL AS NVARCHAR(2000) SET @SQL =N'xp_cmdshell ''dir d:\USerprofileImage /B''' INSERT INTO imagelist (imgfilename) EXEC sp_executesql @SQL
GO
--Import data into target table
DECLARE @SQL AS NVARCHAR(2000) DECLARE @ImgFilename AS VARCHAR(200) DECLARE filelist CURSOR FOR
  SELECT imgfilename
  FROM   imagelist OPEN filelist FETCH NEXT FROM filelist INTO @ImgFilename WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SET @SQL = 'insert into  UserInfo(LogonName,ProfileImage)
select
reverse(substring(REVERSE('''+@ImgFilename +''') ,charindex(''.'',REVERSE('''+@ImgFilename+''' ),1)+1,LEN('''+@ImgFilename+'''))) ,
(SELECT img.bulkcolumn FROM OPENROWSET(BULK ''d:\USerprofileImage\'+@ImgFilename +''',SINGLE_BLOB) AS img)'
       EXEC sp_executesql @SQL
       FETCH next FROM FileList INTO @ImgFilename
  END
CLOSE filelist DEALLOCATE filelist
GO--Drop the worktable
DROP TABLE imagelist