none
How to import photos and the file name into SQL server table

    Question

  • Hi,

    I was tasked to load several thousand of photos into the SQL Server. Those photos are saved in a specific folder. I want to store the file name in one column and the photo in another column. It would take month for me to enter one by one.

    Is there a way to loop through a given folder and add the file name and photo into the tables using TSQL? 

    Please help!

    Sam

     

    Friday, January 22, 2016 5:17 AM

Answers

  • You can do this in SSIS very easily as well.  One way is to create a file with the path to the images.

    https://www.mssqltips.com/sqlservertip/2616/import-multiple-images-to-sql-server-using-ssis/

    A second way is to use this approach, with one modification.  Wrap the data flow in a foreach loop container.  You will need to change the data flow by changing the data source with a script component that simply reads the variable and outputs it to a column that the import column transformation can use.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, January 22, 2016 12:38 PM

All replies

  • Yes You have to Write two methods for that one is for retrieving file names and their location and pass it to another method to insert into SQL Server table once you execute this code it will start inserting records.

    Just remeber in start make a test folder and put 4-5 files in it to test run first time until and unless you are sure that your code will work fine.

    Images file will be inserted in byte datatype column or in binary format you have to conver image file into binary format.

    public void GetFileNames(string _pathofImageFolder() {}

    public void InsertFiles(string ImageFileName , byte ImageFile) {}


    Umair Aslam Bhatti MCP Blog | Twitter

    Friday, January 22, 2016 5:50 AM
  • I have a script in my server which is loading every night tousands of photos from one location into a table.
    After this loading the server is sending an email with the status.
    I have attached the procedure in order to see how I did it, hopefuly will help
    USE [ISB_User]
    GO
    /****** Object:  StoredProcedure [dbo].[PROC_INSERT_PIC_IN_DATABASE]    Script Date: 22.01.2016 08:44:30 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		Ciprian LUPU
    -- Create date: 24.05.2013
    -- Description:	XCopiere poze dintr-o locatie in baza de date
    -- =============================================
    ALTER PROCEDURE [dbo].[PROC_INSERT_PIC_IN_DATABASE] 
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @mailBODY NVARCHAR(MAX)
    	SET @mailBODY = 
    	'
    		<html>
    		<head>
    			<meta charset="utf-8" />
    			<title></title>
    		</head>
    		<body>
    
    			<table style="width:200px; font-family: Century Gothic; font-size: medium; border-top-style: dotted; border-top-width: thin; border-top-color: #C0C0C0; border-bottom-style: dotted; border-bottom-width: thin; border-bottom-color: #C0C0C0;">
    				<tr>
    					<td style="background-color: #FFFF99; font-weight: bold;">Numar marca</td>
    				</tr>
    	'
    
    	DECLARE @picture NVARCHAR(1024)
    	DECLARE @location NVARCHAR(1024)
    	SET @location = 'D:\fotoHR\''
    
    	DECLARE @pictureLocation NVARCHAR(1024)
    	DECLARE @sql NVARCHAR(MAX)
    
    	DELETE FROM dbo.Pict
    	WHERE [PictEmplPersNo] NOT IN
    		(
    			SELECT [dbo].[sapHR_anexa].[employeePersonnelNumber] FROM [dbo].[sapHR_anexa]
    		)
    
    	-- cursor
    	DECLARE nrMarca CURSOR FOR 
    	SELECT DISTINCT [employeePersonnelNumber] FROM [dbo].[sapHR]
    	WHERE [employeePersonnelNumber] NOT IN
    		(
    			SELECT [dbo].[sapHR_anexa].[employeePersonnelNumber] FROM [dbo].[sapHR_anexa]
    		)
    
    	DECLARE @nrMarca VARCHAR(8)
    	OPEN nrMarca
    	FETCH nrMarca into @nrMarca
    	WHILE @@fetch_status <> -1
    	BEGIN
    		
    		--SET @picture = CAST(CAST(@nrMarca AS INT)-80000000 AS NVARCHAR(8))
    		SET @picture = @nrMarca
    		SET @pictureLocation = 'DIR ' + @location + @picture + '.jpg /B'
    
    		DECLARE @output INT
    		EXEC @output = XP_CMDSHELL @pictureLocation, NO_OUTPUT
    		IF @output = 1
    			BEGIN	
    				PRINT 'File do not exists'
    				IF (CAST(@nrMarca AS INT) < 80009999)
    				BEGIN
    					SET @mailBody = @mailBody + 
    					'
    						<tr>
    							<td>' + @nrMarca +'</td>
    						</tr>
    					'
    				END
    			END
    		ELSE
    			BEGIN
    				SET @sql = 
    				'
    				INSERT INTO [dbo].[Pict]([PictEmplPersNo], [PictFoto])
    				SELECT ''' + @nrMarca + ''', * FROM OPENROWSET(BULK ''' + @location + @picture + '.jpg' + ''', SINGLE_BLOB) as tempImg 
    
    				'
    				EXECUTE (@sql)
    			END
    
    		FETCH nrMarca INTO @nrMarca
    	END
    	CLOSE nrMarca
    	DEALLOCATE nrMarca
    
    	SET @mailBody = @mailBody + 
    	'
    			</table>
    		</body>
    		</html>
    	'
    
    	DECLARE @mail_profile_name nvarchar(MAX)		
        SET @mail_profile_name = 'ISB-SQL-Server'
    
    	DECLARE @mail_subiect nvarchar(MAX)
    	SET @mail_subiect = 'Result Pictures Import - ISB_User [PROC_INSERT_PIC_IN_DATABASE]'
    
    	DECLARE @mailTO NVARCHAR(MAX)
    	SET @mailTO = 'sancucrn@schaeffler.com'
    
    	DECLARE @mailCC NVARCHAR(MAX)
    	SET @mailCC = 'lupucpr@schaeffler.com'
    
    	-- trimitere e-mail
    	EXEC msdb.dbo.sp_send_dbmail 
    		@profile_name = @mail_profile_name, 
    		@recipients = @mailTo, 
    		@copy_recipients = @mailCC,
    		@subject = @mail_subiect, 
    		@importance = 'NORMAL', 
    		@body = @mailBODY,
    		@body_format = 'HTML'
    
    
    END
    
    


    Friday, January 22, 2016 6:46 AM
  • Why on Earth would you do this in T-SQL? Obviously the task should be automatied, but why T-SQL? That's not how you read files.

    Here is a very simple example how to load a single file in VB .NET,
    http://www.sommarskog.se/blobload.txt

    What you need to add is an outer loop that iterates over the directory; that is a fairly simple thing to write, as there are classes in .NET for this. (But the names escapes me now.)

    Friday, January 22, 2016 9:35 AM
  • Which SQL Server version you are using?

    If you are on SQL Server 2012 and ahead, you can use FileTables feature, which is built on top of FileStream and very easy to implement.

    FileTable feature does not store files (or images in your case) in SQL Database, but in a secure Windows File-System which is only visible via SSMS.

    Check my blog post on FileTables: http://sqlwithmanoj.com/2012/02/28/sql-server-2012-a-k-a-denali-new-feature-filetables/

    After implementing FileTables feature you just need to copy-paste all these images or any kind of file on this folder and you are done.

    The retrieval of data is very easy and you get all the properties stored in the metadata files.


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page

    Friday, January 22, 2016 11:15 AM
  • You can do this in SSIS very easily as well.  One way is to create a file with the path to the images.

    https://www.mssqltips.com/sqlservertip/2616/import-multiple-images-to-sql-server-using-ssis/

    A second way is to use this approach, with one modification.  Wrap the data flow in a foreach loop container.  You will need to change the data flow by changing the data source with a script component that simply reads the variable and outputs it to a column that the import column transformation can use.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, January 22, 2016 12:38 PM