Friday, February 3, 2012

SQL Server 2005/2008 : Import Blob or images using TSQL

SQL Server 2005/2008: Import images using TSQL
SQL Server 2005/2008: Import blob using TSQL

There are several options to store BLOB or images data type in SQL Server database. Also there are several options in insert and export BLOB or image data types. But I always prefer the TSQL methods since I can write stored procedures and use those. There are some VB Script options too so import and export BLOB/Images into SQL Server database.

First lets look at a table that has a BLOB datatype column
CREATE TABLE [dbo].[Images](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [document] [image] NULL)

To insert images/blob data into SQL Server 2005 and 2008 use the following query

INSERT INTO Images(document)  
SELECT * 
FROM OPENROWSET(BULK N'C:\Pictures\IMG_1.jpg', SINGLE_BLOB) as tempImg

If you have multiple files that you want to insert then use a cursor to go through each file and insert into the table.

DECLARE @imgString varchar(80), @imgNumber int
DECLARE @insertString varchar(3000)

SET @imgNumber = 1
WHILE (@imgNumber < 14)
BEGIN
    SET @imgString = 'C:\Pictures\IMG_' + CONVERT(varchar,@imgNumber) + '.jpg'
    SET @insertString = N'INSERT INTO Images(document)
                        SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'
    EXEC(@insertString)
    SET @imgNumber = @imgNumber + 1
END

I have had difficulty in extracting the filename and the extension once it is inserted into the table. So I highly recommend that the table have a column for the file name and path where it was imported from. And these values be inserted into the table. So create a table like this.

CREATE TABLE [dbo].[Images](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Filename] varchar(100),
    [Path] varchar(200),
    [document] [image] NULL    )

This way when you extract it then you do not have to worry about what the destination filename should be and what was the extension of the file.

3 comments:

  1. Sir I need to upload many images to database, but they r not in correct order, if 1.jpg is present then 2.jpg is not and next is 3.jpg. so while looping the pgm check 2.jpg and it is null, so the program shows error.

    ReplyDelete
    Replies
    1. There a couple of workarounds for your problem.
      1) I would PRINT the statements instead of EXEC(@insertString). Then I would delete all the statements that are null because the image is not there. Then I would execute the remaining ones.

      2) Assuming that there are too many images that are missing then the other method would be to use some OS command that would read the list of files in a directory. Then pass those file names in the while loop. You will have to modify the script to do that.

      3) You can also use CATCH and TRY commands to capture any errors and print that. This way you will have the option of moving to the next image even if you get an error with the previous one.

      Delete
    2. sir currently I am using this code to insert many images, but its not working, could u pls check.


      DECLARE @CODE nvarchar(10)
      DECLARE image_cursor CURSOR FOR
      SELECT CODE FROM Mycode WHERE images IS NULL
      OPEN image_cursor;

      FETCH NEXT FROM image_cursor
      INTO @CODE;

      WHILE @@FETCH_STATUS = 0
      BEGIN
      DECLARE @sql VARCHAR(MAX)

      DECLARE @imagePath VARCHAR(255)
      SET @imagePath = 'D:\images\' + RTRIM(LTRIM(@CODE)) + '.jpg'

      SET @sql = 'UPDATE Mycode'
      SET @sql = @sql + 'SET images = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS images), SET PictureFileName = ' + @imagepath
      SET @sql = @sql + 'WHERE CODE = ''' + (@CODE) + ''';'

      BEGIN TRY
      EXECUTE sp_executesql @sql
      END TRY
      BEGIN CATCH

      END CATCH

      FETCH NEXT FROM image_cursor
      INTO @CODE;
      END
      CLOSE image_cursor;
      DEALLOCATE image_cursor;

      SELECT CODE, images FROM Mycode
      WHERE images IS NOT NULL

      Delete