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 






5 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. An image editing administration can change any clear image into a specialist looking one. clipping path company

    ReplyDelete
  3. This is generally awful, as photos are an essential piece of any showcasing effort and the main impetus that advances the film through promoting, print editorials, and on the web.
    amazon photo editing service

    ReplyDelete
  4. "AI-generated art could inspire new forms of storytelling." ai image video

    ReplyDelete