Pages

Wednesday 14 March 2012

Importing images to SQL server database using T-Sql

Yesterday evening one of the developer  asked me that he need a help in importing images into a   table. It was interesting question for me as I never thought about the possibility of importing images to database table using a T-SQL. I invested some time and found that OPENROWSET is the solution which is the best way to open any non sql data sources.

 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




12 comments:

  1. Thank you very much, your solution is great!

    ReplyDelete
  2. Thank you for this valuable information. The information you shared is very interesting. Get your business to the next level in simple steps. We provides lowest price of erp software Software for our clients.
    erp software in chennai | erp providers in chennai | online events registration

    ReplyDelete
  3. Interesting post! This is really helpful for me. I like it! Thanks for sharing!
    seo lüdenscheid

    ReplyDelete
  4. Hello there,

    am looking for a solution where i can read characters or integers from a JPG image(taken by android camera) and i read these into some sort of table or database like excel, MS Access, SQL or equivalent..
    i saw some OCR options on the internet but these do not seem to address what i want to achieve..

    ReplyDelete
  5. Exceptionally valuable information.

    ReplyDelete

  6. zapya for windows
    zapya pc
    zapya apk download
    zapya free download
    Great.. Tutorial is just awesome..It is really helpful for a newbie like me.

    ReplyDelete


  7. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to article very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    DedicatedHosting4u.com

    ReplyDelete
  8. thanks for sharing such a wonderful information from this post
    digital marketing

    ReplyDelete
  9. kingroot for pc
    kingroot pc
    download kingroot pc
    kingroot apk
    kingoroot
    kingroot download
    We don’t say Kingroot does that, but it’s kind of unknown and that’s what makes the app vulnerable

    ReplyDelete
  10. =https://mailsguide.com/aol-mail-sign-up-login
    Still others are geared towards accessibility and cross-platform, mobile access

    ReplyDelete