This article examines how to upload multiple files to a
SQL Server table. There are many ways to do this. The method I would like to
introduce takes advantage of the OSQL.exe utility and the TEXTCOPY.exe utility.
Pre-requisite
- SQL Server
2000 client is installed on the machine where you are running this batch file
- SQL Login used
has at least DBO permission on the database
Step 1
Create C:\Upload folder
Step 2
Copy TextCopy.exe from \Program Files\Microsoft SQL
Server\MSSQL\Binn to C:\Upload folder [Ref Fig 1.0]
Step 3
Copy and paste the code below into C:\Upload\Upload.bat
@ECHO off
cls
REM ECHO on
REM Objective: To upload all the files in the folder to SQL server as a table
REM Created by: MAK
REM Date: Nov 3, 2004
REM Contact: mak_999@yahoo.com
REM Check parameters
if "%1"=="" Goto noparmErr
if "%1"=="/?" Goto Help
REM assign variables
set Sourcefolder=%1
set servername=%2
set Databasename=%3
set loginname=%4
set password=%5
set LogFile=%6
Date/t > %6
time /t >> %6
REM Export DIR listing to C:\upload\Dirlist.txt
dir %1 /b > C:\upload\Dirlist.txt
OSQL /S%2 /U%4 /P%5 /d%3 /Q"if exists (select * from %3.dbo.sysobjects where id =
object_id(N'[Myuploadtable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table
%3.dbo.[Myuploadtable] " >> %6
OSQL /S%2 /U%4 /P%5 /d%3 /Q"create table %3.dbo.Myuploadtable (id varchar(500),
ImageFiles Image)" >> %6
FOR /F "usebackq delims==" %%i in (C:\upload\Dirlist.txt)
do OSQL /S%2 /U%4 /P%5 /Q"insert into %3.dbo.Myuploadtable (id,ImageFiles)
values('%%i',' ') " >> %6
FOR /F "usebackq delims==" %%i in (C:\upload\Dirlist.txt)
do c:\upload\textcopy.exe /S%2 /U%4 /P%5 /D%3 /Tmyuploadtable /CImagefiles
/F"%1%%i" /I /W"where [id]='%%i'" >> %6
GOTo END2
:noparmErr
Echo Usage help: example as follows
echo ...
Echo Objective: To upload all the files in the folder to SQL server Table
Echo Created by: MAK
Echo USAGE:
ECHO ....
ECHO ....
Echo %0 SourceFolder Servername Databasename Login Password LogFile
Goto END2
:Help
Echo Usage help: example as follows
echo ...
Echo Objective: To upload all the files in the folder to SQL server Table
Echo Created by: MAK
Echo USAGE:
ECHO ....
ECHO ....
Echo %0 SourceFolder Servername Databasename Login Password Logfile
Goto END2
:END
Echo "Compare Query results Completed" >>%LogFile%
date/t >> %LogFile%
time/t >> %LogFile%
goto END2
:END2
Download upload.bat
Step 4
Execute
the batch file. [Ref Fig 1.1]
[Fig 1.1]
Step 5
Execute
the batch file with the correct parameters [Ref Fig 1.2]. When executed with the correct parameters,
this batch file calls the OSQL utility and creates [Myuploadtable] on the given
database. Then, using the same OSQL utility it inserts rows into the table. The
number of rows and the number of source files will be same, the values in the
column is the same as the name of the files in the folder [Ref Fig 1.4,
1.5. Then it calls the
TEXTCOPY utility and uploads every file in the source folder [Ref Fig
1.3] to the SQL server
table.
[Fig 1.2]
Parameters Explained
| Upload | - | Batch File Name |
| C:\myfiles\ | - | Folder where all the source files are stored |
| SQL | - | SQL Server instance name where all the files are going to be uploaded |
| MyDB | - | Database
Name where all the files are going to be stored. |
| sa | - | Login
name of SQL Server box |
| yeahright | - | Password
for the login sa |
| x.log | - | Log
file where all the process information is stored while executing this batch file |
[Fig 1.3]
Select ID from Myuploadtable.
|
01-2004-08-20-.wav
|
|
Pfizer
12625.doc
|
|
STARTSQL.bat
|
|
status.bat
|
|
STOPSQL.bat
|
|
sysperfinfo.zip
|
|
Tech
Interview.xls
|
|
Unix.bat
|
|
Taiwan.zip
|
[Fig 1.4]
select * from Myuploadtable
|
01-2004-08-20-.wav
|
0x5249464618990A00574
|
|
Pfizer
12625.doc
|
0xD0CF11E0A1B11AE
|
|
STARTSQL.bat
|
0x6E65742073746F7D
|
|
status.bat
|
0x6E6574207374617SDF
|
|
STOPSQL.bat
|
0x6E65742073746FDF
|
|
sysperfinfo.zip
|
0x5249464618990A00574
|
|
Tech
Interview.xls
|
0xD0CF11E0A1B11AEDF
|
|
Unix.bat
|
0x6E65742073746F7DER
|
|
Taiwan.zip
|
0x5249464618990A00DE
|
[Fig 1.5]
Step 6
Rename the table.
sp_rename 'Myuploadtable','Prod_ImageTable'
When you
run the batch file again, it will drop and recreate the [Myuploadtable] table.
That is why we are renaming the table.
The batch
file also creates a log file as shown below.
Thu 11/04/2004
9:16a
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\01-2004-08-20-.wav'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\Pfizer 12625.doc'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\STARTSQL.bat'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\status.bat'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\STOPSQL.bat'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\sysperfinfo.zip'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\Taiwan.zip'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\Tech Interview.xls'.
TEXTCOPY Version 1.0
DB-Library version 8.00.194
Data copied into SQL Server image column from file 'c:\myfiles\Unix.bat'.
Conclusion
As
mentioned earlier, the intent of this article has been to guide SQL Server
developers and administrators in uploading multiple files to a SQL Server
table.
»
See All Articles by Columnist MAK
» 
