Upload multiple files to VarBinary column in SQL Server 2005

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
/****** object: database [test] script date: 09/10/2006 22:07:03 ******/
if exists (select name from sys.databases where name = N’test’)
drop database [test]
go
create database [test]
go
use [test]
/****** object: table [dbo].[myimage] script date: 09/10/2006 21:55:46 ******/
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
/****** Object: StoredProcedure [dbo].[usp_uploadfiles]
Script Date: 09/10/2006 23:33:34 ******/
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 [test].[dbo].[myblob] ([Image Name]) values (“Blue Lace 16.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Blue Lace 16.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Blue Lace 16.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Coffee Bean.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Coffee Bean.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Coffee Bean.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“FeatherTexture.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\FeatherTexture.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”FeatherTexture.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Gone Fishing.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Gone Fishing.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Gone Fishing.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Greenstone.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Greenstone.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Greenstone.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Prairie Wind.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Prairie Wind.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Prairie Wind.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Rhododendron.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Rhododendron.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Rhododendron.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“River Sumida.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\River Sumida.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”River Sumida.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Santa Fe Stucco.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Santa Fe Stucco.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Santa Fe Stucco.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Soap Bubbles.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Soap Bubbles.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”Soap Bubbles.bmp”
Insert into [test].[dbo].[myblob] ([Image Name]) values (“Zapotec.bmp”)
update [test].[dbo].[myblob] set [blob] = (SELECT *
FROM OPENROWSET(BULK “c:\windows\Zapotec.bmp”, SINGLE_BLOB)AS x ) WHERE [Image Name]=”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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles