Below are the steps read all the images from a specified folder using stored procedure.
1). Create Table
GO
CREATE TABLE [dbo].[TBLImages] (
[ImageId] [int] IDENTITY (1, 1) NOT NULL ,
[OriginalPath] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ImageData] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
2). Create Stored Procedure
Create procedure SPCopyImagesToTable @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
BEGIN
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
INSERT INTO [dbo].[ImageInsert]([OriginalPath], [ImageData])
Select @Filepath+@Filename,(SELECT * FROM OPENROWSET(BULK @Filepath+@Filename, SINGLE_BLOB))AS Images
end
drop table #y
END
3). Execute Store procedure.
Exec SPCopyImagesToTable 'd:\images\', '*.jpg', 'TBLImages'