Insert Multiple Images into a Image column in sql server

Question

I am trying to insert multiple images into a Image datatype column in sql server 2012.

The folder has multiple .jpg and .png files.

I am currently using this sql to insert one by one

How can I insert multiple images or the whole folder thanks.

INSERT INTO Images(ImageId, ItemId, Caption, Image)
Values(NEWID(), null, null,(  
SELECT * FROM OPENROWSET(
   BULK 'C:\Images\image004.jpg',
   SINGLE_BLOB) AS x))

Here is the table with clustered index on the ClusterKey column

CREATE TABLE [dbo].[Images](
    [ImageId] [uniqueidentifier] NOT NULL,
    [ItemId] [uniqueidentifier] NULL,
    [Caption] [nvarchar](100) NULL,
    [Image] [image] NOT NULL,
    [ClusteredKey] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Images] PRIMARY KEY NONCLUSTERED 
(
    [ImageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Currently Trying this approach

--Table to process the files

CREATE TABLE imagelist 
  ( 
     imgfilename VARCHAR(200) 
  ) 
GO 
--Put all file name in a table for easy processing

DECLARE @SQL AS NVARCHAR(2000) 
SET @SQL =N'xp_cmdshell ''dir c:\Images /B''' 
INSERT INTO imagelist (imgfilename) EXEC sp_executesql @SQL
GO
--Import data into target table

DECLARE @SQL AS NVARCHAR(2000) 
DECLARE @ImgFilename AS VARCHAR(200) 
DECLARE filelist CURSOR FOR 
  SELECT imgfilename 
  FROM   imagelist 

OPEN filelist 

FETCH next FROM filelist INTO @ImgFilename 

WHILE ( @@FETCH_STATUS = 0 ) 
  BEGIN 
      SET @SQL = 'INSERT INTO Images(ImageId, Image)   Values(NEWID(),( select  reverse(substring(REVERSE(''' 
                 + @ImgFilename 
                 + ''') ,charindex(''.'',REVERSE(''' 
                 + @ImgFilename + ''' ),1)+1,LEN(''' 
                 + @ImgFilename 
                 + '''))) , (SELECT img.bulkcolumn FROM OPENROWSET(BULK ''c:\Images\' 
                 + @ImgFilename + ''',SINGLE_BLOB) AS img))' 

      EXEC Sp_executesql 
        @SQL 

      FETCH next FROM filelist INTO @ImgFilename 
  END 

CLOSE filelist 

DEALLOCATE filelist

From whence do you obtain the file names? Are they in a table? — HABO
– HABO, Commented May 29, 2013 at 21:16
Stored locally in a images folder. The names can be image001.jpg, image002.png, image003.jpg etc. in the folder. — KeyboardFriendly
– KeyboardFriendly, Commented May 29, 2013 at 21:34
incorrect syntax near 'INSERT INTO Images(ImageId, Image) Values(NEWID(),( select — KeyboardFriendly
– KeyboardFriendly, Commented May 30, 2013 at 2:18
You tell SQL Server that you'll insert ImageId and Image, then you insert a GUID, a filename (without type) and the contents of the file. Three columns of data into two table columns? — HABO
– HABO, Commented May 30, 2013 at 3:26

HABO · Accepted Answer · 2013-05-29 23:44:09Z

1

This shows one way to import multiple files where the filenames can be calculated:

declare @Count as Int = 3622;
declare @Filename as VarChar(128);
declare @Statement as VarChar(256)

while @Count <= 3625
  begin
  set @Filename = 'C:\Photos\IMG_' + Right( '000' + Cast( @Count as VarChar(5) ), 4 ) + '.JPG';
  set @Statement = 'select * from OpenRowSet( Bulk ''' + @Filename + ''', Single_Blob ) as Nought';
  execute( @Statement )
  set @Count = @Count + 1;
  end;

Not pretty, but if this is a one-time import then it may not matter.

answered May 29, 2013 at 23:44

HABO

15.9k5 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert Multiple Images into a Image column in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related