How To Import Images To SQL Server with TSQL Codes

dbtut

5 years ago

In this article we will import the image files in a folder into a table in the sql server database.

First, we create the following table.

CREATE TABLE [dbo].[words](
[word] [nvarchar](1000) NULL,
[file] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[words](

[word] [nvarchar](1000) NULL,

[file] [varbinary](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Let’s add a few records to the Table.

INSERT INTO [dbo].[words] ([word],[file]) VALUES ('warnof',NULL)
INSERT INTO [dbo].[words] ([word],[file]) VALUES ('turn out',NULL)

1 2	INSERT INTO [dbo].[words] ([word],[file]) VALUES ('warnof',NULL) INSERT INTO [dbo].[words] ([word],[file]) VALUES ('turn out',NULL)

We then copy two image files in a folder called warnof.jpg and turn out.jpg.

In the following query, we assume that the images are copied under the “C:\images” folder. When we run our query in this way, we import the images to the database.

DECLARE @files table (ID int IDENTITY, FileName varchar(100))

insert into @files execute xp_cmdshell 'dir C:\images /b'

DECLARE @FileName varchar(MAX);
DECLARE db_cursor CURSOR FOR 

select FileName from @files where FileName is not null

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @FileName  

WHILE @@FETCH_STATUS = 0  
BEGIN  
DECLARE @FileName2 varchar(max);
DECLARE @FileName3 varchar(max);

Select @FileName2=@FileName
select @FileName3= LEFT(@FileName, LEN(@FileName) - 4)

DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'UPDATE dbo.words'
    SET @sql = @sql + 'SET file= (SELECT BulkColumn FROM OPENROWSET( BULK ''C:\images\'+ @FileName2+' '', Single_Blob) AS picture) '
       SET @sql = @sql + 'WHERE word = ''' + @FileName3+''''
       print @sql
       FETCH NEXT FROM db_cursor INTO @FileName  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

DECLARE @files table (ID int IDENTITY, FileName varchar(100))

insert into @files execute xp_cmdshell 'dir C:\images /b'

DECLARE @FileName varchar(MAX);

DECLARE db_cursor CURSOR FOR

select FileName from @files where FileName is not null

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @FileName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @FileName2 varchar(max);

DECLARE @FileName3 varchar(max);

Select @FileName2=@FileName

select @FileName3= LEFT(@FileName, LEN(@FileName) - 4)

DECLARE @sql NVARCHAR(MAX)

SET @sql = 'UPDATE dbo.words'

SET @sql = @sql + 'SET file= (SELECT BulkColumn FROM OPENROWSET( BULK ''C:\images\'+ @FileName2+' '', Single_Blob) AS picture) '

SET @sql = @sql + 'WHERE word = ''' + @FileName3+''''

print @sql

FETCH NEXT FROM db_cursor INTO @FileName

END

CLOSE db_cursor

DEALLOCATE db_cursor