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 |
Any script compatible with MySQL?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteAn image editing administration can change any clear image into a specialist looking one. clipping path company
ReplyDeleteThis 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.
ReplyDeleteamazon photo editing service
"AI-generated art could inspire new forms of storytelling." ai image video
ReplyDelete