如何使用MSSQL 2000读取/写入文件？

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'.

在MySQL中,您可以使用SELECT load_file('foo.txt')简单地读取文件,并使用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.

我正在寻找一种在MSSQL 2000中执行此操作的方法,而无需使用任何外部工具,只需查询或存储过程。

3 个解决方案

#1

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

对于输入,您可以使用文本文件ODBC驱动程序:

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:

假设输出文件已经存在,您可以使用Jet写入它:

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

#2

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

或者,您可以从命令行使用bcp来读取/写入文件,这通常可以更快地进行批量插入/选择

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. :)

或者,您也可以使用sp_OACreate,但您可能不想使用此路由,因为它不完全是数据库的意图。 :)

#3

You can also use BULK INSERT to read files.

您还可以使用BULK INSERT来读取文件。

#1