Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

PracticalSQLDba

I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.

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




Comments

Leave a comment on the original post [www.practicalsqldba.com, opens in a new window]

Loading comments...