SQL Server provides several "standard" techniques for file manipulation but, sometimes, they aren't quite up to the task at hand – especially when dealing with large strings or relatively unstructured data. Here are a number of T-SQL Stored Procedures, based on the use of the FileSystem Object (FSO) that may just get you out of a tight corner.
SQL Server has never been short of ways to read from files, and it is always better to use the standard techniques provided by SQL Server where possible. However, most of them are really designed for reading tabular data, and aren't always trouble-free when used with large strings or relatively unstructured data.
For reading tabular data from a file, whether character-delimited or binary, there is nothing that replaces the hoary old Bulk Copy Program (BCP), which has more esoteric methods such as Bulk Insert. It is possible to read text-based delimited files with ODBC; simple files can be read and written-to using the
xp_cmdshell Stored Procedure.
Using the code
Thankfully, when armed with OLE Automation and the FileSystem Object (FSO), all sorts of things are possible. The FileSystem Object was introduced into Windows to provide a single common file-system COM interface for scripting languages. It provides a number of handy services that can be accessed from T-SQL. In this article, I provide an example Stored Procedure that uses this interface to allow you to:
- Read lines of text from a file.
- Read a file into a SQL Server data type.
I'll provide a few details on the FSO along the way, but let's start with some examples of some of these procedures in action. You'll need to enable OLE Automation on your test server in order to follow along.
This is all very well, but how about something that reads a file in one gulp into a
XML data type? Perhaps you need to extract data from HTML, XHTML or some other format. I wrote a Stored Procedure
dbo.spREADFileStream to read a file as a string. A code example is given below: