--------------------------------------------------------------------------------
Insert File to DB

以下的範例是使用 SQL Server 2005

方法 
1. 建立 Image 欄位 
在 testDB 建立一個 ImportImage Table 並新增一個 image 欄位，資料型態為 Image

2. BULK INSERT 圖檔語法

INSERT INTO ImportImage 
SELECT * FROM OPENROWSET( 
BULK 'D:\d2.jpg', SINGLE_BLOB) AS x

執行結束後出現 (1 row(s) affected)

3. 確認結果


這只是一個簡單的 BULK INSERT 圖檔、文字檔甚至 DOC 檔的一個範例，歡迎大家討論！
--------------------------------------------------------------------------------
USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60), 
  FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document) 
   SELECT 'Text1.txt' AS FileName, 
      '.txt' AS FileType, 
      * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO




--------------------------------------------------------------------------------
Upload multiple files to VarBinary column in SQL Server 2005
By Muthusamy Anantha Kumar aka The MAK


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.