Script to Upload Multiple Images in Sql Table
This article discusses how to upload multiple prototype or text files to the SQL Server database varbinary column.
Pre-requisite
a. Make certain xp_cmdshell is enabled. If information technology is non enabled, execute the following command.
use master
go
sp_configure 'xp_cmdshell',1
go
reconfigure with override
go
b. Brand 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 1 column in the table can hold the value of the filename.
set quoted_identifier off
become
use [master]
go
/****** object: database [test] script appointment: 09/10/2006 22:07:03 ******/
if exists (select proper name from sys.databases where proper name = North'test')
drop database [examination]
go
create database [exam]
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(Northward'[myblob]') and blazon in (North'u'))
driblet table [myblob]
get
create tabular array [myblob] (
[id] int identity(one,i),
[prototype name] varchar(100),
[blob] varbinary(max))
Become
Let us presume that nosotros desire to upload all the .bmp files from the C:\Windows folder to the tabular array "myblob" in the schema "dbo" on the "test" database.
Footstep 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 chosen for whatever database.
USE [master]
Get
/****** Object: StoredProcedure [dbo].[usp_uploadfiles]
Script Appointment: 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
fix 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
ready 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 '[email protected][email protected]
create table #dir (proper name varchar(1500))
insert #dir(proper noun) exec master..xp_cmdshell @dircommand
delete from #dir where proper name is NULL
create table #dir2 (id int identity(1,i),proper noun varchar(1500))
insert into #dir2 select proper name from #dir
–select * from #dir2
set @maxcount = ident_current('#dir2')
while @count <[electronic mail protected]
begin
fix @filename =(select name from #dir2 where id = @count)
fix @insertquery = 'Insert into ['[email protected]+'].['[email protected]+'].['[email protected]+']
([' [email protected] +']) values ("'[email protected]+'")'
set @updatequery = 'update ['[e-mail protected]+'].['[email protected]+'].['[e-mail protected]+']
ready ['[electronic mail protected]+'] =
(SELECT * FROM OPENROWSET(BULK "'[email protected][electronic mail protected]+'", SINGLE_BLOB)AS x )
WHERE ['[e-mail protected] +']="'[email protected]+'"'
if @printorexec ='print'
begin
print @insertquery
impress @updatequery
end
if @printorexec ='exec'
begin
exec (@insertquery)
exec (@updatequery)
end
prepare @count = @count +ane
terminate
go
This procedure accepts the post-obit 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 exist
uploaded
@FileNameColumn = Proper noun 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 hulk information
@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 information technology will
generate and display the commands. If "Exec" is
passed as a parameter information technology volition execute the
command directly–meaning upload all the files.
Pace 2
Let us execute this stored process by passing the post-obit parameters equally shown beneath.
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 every bit shown below.
Insert into [test].[dbo].[myblob] ([Image Proper noun]) values ("Blue Lace 16.bmp")
update [test].[dbo].[myblob] set [hulk] = (SELECT *
FROM OPENROWSET(Majority "c:\windows\Blueish Lace 16.bmp", SINGLE_BLOB)Equally x ) WHERE [Image Proper noun]="Blueish Lace xvi.bmp"
Insert into [test].[dbo].[myblob] ([Prototype Name]) values ("Coffee Bean.bmp")
update [test].[dbo].[myblob] set up [blob] = (SELECT *
FROM OPENROWSET(Bulk "c:\windows\Coffee Bean.bmp", SINGLE_BLOB)AS ten ) WHERE [Paradigm Name]="Java Edible bean.bmp"
Insert into [exam].[dbo].[myblob] ([Image Name]) values ("FeatherTexture.bmp")
update [test].[dbo].[myblob] set [hulk] = (SELECT *
FROM OPENROWSET(BULK "c:\windows\FeatherTexture.bmp", SINGLE_BLOB)AS x ) WHERE [Prototype Proper noun]="FeatherTexture.bmp"
Insert into [test].[dbo].[myblob] ([Paradigm Name]) values ("Gone Fishing.bmp")
update [test].[dbo].[myblob] gear up [blob] = (SELECT *
FROM OPENROWSET(Majority "c:\windows\Gone Angling.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Gone Angling.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)Equally x ) WHERE [Paradigm Name]="Greenstone.bmp"
Insert into [test].[dbo].[myblob] ([Paradigm Proper name]) values ("Prairie Current of air.bmp")
update [test].[dbo].[myblob] prepare [blob] = (SELECT *
FROM OPENROWSET(Majority "c:\windows\Prairie Air current.bmp", SINGLE_BLOB)AS x ) WHERE [Epitome Name]="Prairie Wind.bmp"
Insert into [exam].[dbo].[myblob] ([Epitome Proper noun]) values ("Rhododendron.bmp")
update [test].[dbo].[myblob] set [hulk] = (SELECT *
FROM OPENROWSET(BULK "c:\windows\Rhododendron.bmp", SINGLE_BLOB)AS x ) WHERE [Image Name]="Rhododendron.bmp"
Insert into [test].[dbo].[myblob] ([Image Proper name]) values ("River Sumida.bmp")
update [test].[dbo].[myblob] prepare [hulk] = (SELECT *
FROM OPENROWSET(BULK "c:\windows\River Sumida.bmp", SINGLE_BLOB)AS 10 ) WHERE [Prototype Proper name]="River Sumida.bmp"
Insert into [test].[dbo].[myblob] ([Epitome 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 ten ) WHERE [Image Proper noun]="Santa Fe Stucco.bmp"
Insert into [test].[dbo].[myblob] ([Image Proper noun]) values ("Soap Bubbling.bmp")
update [test].[dbo].[myblob] set up [blob] = (SELECT *
FROM OPENROWSET(Bulk "c:\windows\Soap Bubbles.bmp", SINGLE_BLOB)Every bit x ) WHERE [Prototype 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 the states execute this stored procedure by passing the post-obit parameters as shown below.
Exec master..usp_uploadfiles
@databasename ='test',
@schemaname ='dbo',
@tablename ='myblob',
@FileNameColumn ='Image Proper 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 blazon specified and upload the respective files.
A successful upload tin exist verified by executing the following query:
Select * from [exam].[dbo].[myblob]
The result of the in a higher place query is displayed in Fig 1.0.
Fig ane.0
Conclusion
This article has examined how to upload multiple prototype or text files to SQL server database varbinary column.
» Encounter All Articles by Columnist MAK
bullenhernione1971.blogspot.com
Source: https://www.databasejournal.com/ms-sql/upload-multiple-files-to-varbinary-column-in-sql-server-2005/