How can I read/write from/to files with MSSQL 2000?

Question

In MySQL you can quite simply read a file with SELECT load_file('foo.txt') and write to a file with SELECT 'text' INTO DUMPFILE 'foo.txt'.

I am looking for a way to do this in MSSQL 2000 without using any external tools, just with queries or stored procedures.

Answer 1

There are a few ways you can do this.

using xp_cmdshell:

declare @writetofile varchar(1000) select @writetofile = 'osql -U -P -S -Q"select * from yourtable" -o"c:\foo.txt" ' exec master..xp_cmdshell @writetofile

Or you can use bcp from the command line to read/write to files, which is usually quicker to do bulk inserts/selects

Alternatively, you can also use sp_OACreate.. but you probably don't want to go this route... since it's not exactly what databases are meant to do. :)

Answer 2

For input, you can use the text file ODBC driver:

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from [FileName.csv]')

Assuming the output file already exists, you can use Jet to write to it:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\;HDR=Yes;', 'SELECT * FROM filename.csv') SELECT 1234,5678

Answer 3

You can also use BULK INSERT to read files.