This article discusses how to upload multiple image or text files to the SQL Server database varbinary column.
Pre-requisite
a. Make sure xp_cmdshell is enabled. If it is not enabled, execute the following command.
use master
go
sp_configure ‘xp_cmdshell’,1
go
reconfigure with override
go
b. Make sure to create a table with a varbinary(max) column, since the image column is not going to be supported in future versions. Also, make sure at least one column in the table can hold the value of the filename.
set quoted_identifier off
go
use [master]
go
if exists (select name from sys.databases where name = N’test’)
drop database [test]
go
create database [test]
go
use [test]
if exists (select * from sys.objects
where object_id = object_id(N’[myblob]’) and type in (N’u’))
drop table [myblob]
go
create table [myblob] (
[id] int identity(1,1),
[image name] varchar(100),
[blob] varbinary(max))
Go
Let us assume that we want to upload all the .bmp files from the C:\Windows folder to the table “myblob” in the schema “dbo” on the “test” database.
Step 1
Execute the following script [using copy and paste or download the usp_uploadfiles.sql file]. This creates a stored procedure, usp_uploadfiles, on the master database so that it can be executed and called for any database.
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N’[dbo].[usp_uploadfiles]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[usp_uploadfiles]
go
set quoted_identifier off
go
create procedure usp_uploadfiles
@databasename varchar(128),
@schemaname varchar(128),
@tablename varchar(128),
@FileNameColumn varchar(128),
@blobcolumn varchar(128),
@path varchar(500),
@filetype varchar(10),
@printorexec varchar(5) = ‘print’
as
set nocount on
declare @dircommand varchar(1500)
declare @insertquery varchar(2000)
declare @updatequery varchar(2000)
declare @count int
declare @maxcount int
declare @filename varchar(500)
set @count=1
set @dircommand = ‘dir /b ‘+@path+@filetype
create table #dir (name varchar(1500))
insert #dir(name) exec master..xp_cmdshell @dircommand
delete from #dir where name is NULL
create table #dir2 (id int identity(1,1),name varchar(1500))
insert into #dir2 select name from #dir
–select * from #dir2
set @maxcount = ident_current(‘#dir2’)
while @count <=@maxcount
begin
set @filename =(select name from #dir2 where id = @count)
set @insertquery = ‘Insert into [’+@databasename+’].[’+@schemaname+’].[’+@tablename+’]
([’ +@filenamecolumn +’]) values (“‘+@filename+'”)’
set @updatequery = ‘update [’+@databasename+’].[’+@schemaname+’].[’+@tablename+’]
set [’+@blobcolumn+’] =
(SELECT * FROM OPENROWSET(BULK “‘+@path+@filename+'”, SINGLE_BLOB)AS x )
WHERE [’+@filenamecolumn +’]=”‘+@filename+'”‘
if @printorexec =’print’
begin
print @insertquery
print @updatequery
end
if @printorexec =’exec’
begin
exec (@insertquery)
exec (@updatequery)
end
set @count = @count +1
end
go
This procedure accepts the following parameters:
@databasename = Name of the database where the schema and table
exist.
@schemaname = Schema of the database where the table exists
@tablename = Name of the table where files are going to be
uploaded
@FileNameColumn = Name of the column in the table where the file
name is going to be stored
@blobcolumn = The actual varbinary(max) column where the file is going to be
stored as blob data
@path = Path of all the files that are suppose to be
uploaded. Example “C:\Windows\”
@filetype = Type of file you want to upload. Example
“*.jpeg”
@printorexec = if “Print” is passed as a parameter it will
generate and display the commands. If “Exec” is
passed as a parameter it will execute the
command directly–meaning upload all the files.
Step 2
Let us execute this stored procedure by passing the following parameters as shown below.
Exec master..usp_uploadfiles
@databasename =’test’,
@schemaname =’dbo’,
@tablename =’myblob’,
@FileNameColumn =’Image Name’,
@blobcolumn = ‘blob’,
@path = ‘c:\windows\’,
@filetype =’*.bmp’,
@printorexec =’print’
This will generate all the commands needed for creating a row for each file and updating the row with the proper file as shown below.
Insert into .. () values (“Blue Lace 16.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Blue Lace 16.bmp”, SINGLE_BLOB)AS x ) WHERE =”Blue Lace 16.bmp”
Insert into .. () values (“Coffee Bean.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Coffee Bean.bmp”, SINGLE_BLOB)AS x ) WHERE =”Coffee Bean.bmp”
Insert into .. () values (“FeatherTexture.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\FeatherTexture.bmp”, SINGLE_BLOB)AS x ) WHERE =”FeatherTexture.bmp”
Insert into .. () values (“Gone Fishing.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Gone Fishing.bmp”, SINGLE_BLOB)AS x ) WHERE =”Gone Fishing.bmp”
Insert into .. () values (“Greenstone.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Greenstone.bmp”, SINGLE_BLOB)AS x ) WHERE =”Greenstone.bmp”
Insert into .. () values (“Prairie Wind.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Prairie Wind.bmp”, SINGLE_BLOB)AS x ) WHERE =”Prairie Wind.bmp”
Insert into .. () values (“Rhododendron.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Rhododendron.bmp”, SINGLE_BLOB)AS x ) WHERE =”Rhododendron.bmp”
Insert into .. () values (“River Sumida.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\River Sumida.bmp”, SINGLE_BLOB)AS x ) WHERE =”River Sumida.bmp”
Insert into .. () values (“Santa Fe Stucco.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Santa Fe Stucco.bmp”, SINGLE_BLOB)AS x ) WHERE =”Santa Fe Stucco.bmp”
Insert into .. () values (“Soap Bubbles.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Soap Bubbles.bmp”, SINGLE_BLOB)AS x ) WHERE =”Soap Bubbles.bmp”
Insert into .. () values (“Zapotec.bmp”)
update .. set = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Zapotec.bmp”, SINGLE_BLOB)AS x ) WHERE =”Zapotec.bmp”
Step 3
Let us execute this stored procedure by passing the following parameters as shown below.
Exec master..usp_uploadfiles
@databasename =’test’,
@schemaname =’dbo’,
@tablename =’myblob’,
@FileNameColumn =’Image Name’,
@blobcolumn = ‘blob’,
@path = ‘c:\windows\’,
@filetype =’*.bmp’,
@printorexec =’exec’
This will create one row for each file in the folder based on the file type specified and upload the corresponding files.
A successful upload can be verified by executing the following query:
Select * from [test].[dbo].[myblob]
The result of the above query is displayed in Fig 1.0.
Fig 1.0
Conclusion
This article has examined how to upload multiple image or text files to SQL server database varbinary column.
» See All Articles by Columnist MAK
