Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 | 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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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 |
