Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 14, 2004

Upload multiple files to SQL Server Image column

By Muthusamy Anantha Kumar aka The MAK

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

  1. SQL Server 2000 client is installed on the machine where you are running this batch file
  2. 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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM