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
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.
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.
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),
[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.
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.
ReplyDeleteThere a couple of workarounds for your problem.
Delete1) 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.
sir currently I am using this code to insert many images, but its not working, could u pls check.
DeleteDECLARE @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