How to Import or load all the picture/image files from a folder to SQL Server Table by using TSQL- SQL Server Tutorial

Scenario:

You are working as SQL Server Developer for Insurance Company. They get the claimed signed letters and scan and place in a folder. You need to write the SQL Script that should be able to read those claim files and load into SQL Server table from a folder.

here are my sample files, you can have any type of files such as pdf, bmp,jpg etc.

How to Import Picture files from a folder to SQL Server Table

Let's go ahead and create a table first in which we would like to load the files from a folder.

USE YourDBName
GO
CREATE TABLE dbo.MyPictures
(
PictureId INT IDENTITY PRIMARY KEY,
PictureFileName VARCHAR(100),
PictureData VARBINARY(MAX),
LoadedDateTime DATETIME
)

The below script can be used to load all the files from a folder to above table.

IF OBJECT_ID('tempdb..#FileList') IS NOT NULL
    DROP TABLE #FileList


--Folder path where files are present
Declare @SourceFolder VARCHAR(100)
SET @SourceFolder='C:\Source\'

  CREATE TABLE #FileList (
    Id int identity(1,1),
    FileName nvarchar(255),
    Depth smallint,
    FileFlag bit)

--Load the file names from a folder to a table
   INSERT INTO #FileList (FileName,Depth,FileFlag)
   EXEC xp_dirtree @SourceFolder, 10, 1

   --Use Cursor to loop throught backups files
   --Select * From #FileList
Declare @FileName VARCHAR(500)

   DECLARE Cur CURSOR FOR
  SELECT FileName from #FileList
  where fileflag=1

OPEN Cur
FETCH Next FROM Cur INTO @FileName
WHILE @@FETCH_STATUS = 0
  BEGIN

  DECLARE @InsertSQL NVARCHAR(MAX)=NULL
  --Prepare SQL Statement for insert
  SET @InsertSQL=
  'INSERT INTO dbo.MyPictures(PictureFileName, LoadedDateTime,PictureData)
SELECT '''+@FileName+''',getdate(),BulkColumn 
FROM Openrowset( Bulk '''+@SourceFolder+@FileName+''', Single_Blob) as Image'

--Print and Execute SQL Insert Statement to load file
Print @InsertSQL
EXEC(@InsertSQL)

     FETCH Next FROM Cur INTO @FileName
  END
CLOSE Cur
DEALLOCATE Cur

Let's check the table if all files are loaded successfully from a folder to table.

Select * from dbo.MyPictures

Import files from a folder to SQL Server Table by using T-SQL