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.
ReplyDelete