textcopy.exe可以在sql7.0的安装光盘找到,语法如下If the direction is IN (/I) then the data from the specified 'file' is copied into SQL Server, replacing the existing text or image value. If direction is OUT (/O) then the text or image value is copied from SQL Server into the specified 'file', replacing any existing file.TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]] [/D [database]] [/T table] [/C column] [/W"where clause"] [/F file] [{/I | /O}] [/K chunksize] [/Z] [/?] /S sqlserver The SQL Server to connect to. If 'sqlserver' is n specified, the local SQL Server is used. /U login The login to connect with. If 'login' is not spec a trusted connection will be used. /P password The password for 'login'. If 'password' is not specified, a NULL password will be used. /D database The database that contains the table with the tex image data. If 'database' is not specified, the d database of 'login' is used. /T table The table that contains the text or image value. /C column The text or image column of 'table'. /W "where clause" A complete where clause (including the WHERE keyw that specifies a single row of 'table'. /F file The file name. /I Copy text or image value into SQL Server from 'fi /O Copy text or image value out of SQL Server into ' /K chunksize Size of the data transfer buffer in bytes. Minimu value is 1024 bytes, default value is 4096 bytes. /Z Display debug information while running. /? Display this usage information and exit.You will be prompted for any required options you did not specify.
/** 导入文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'/** 导出文本文件
EXEC master..xp_cmdshell 'bcp dbname..tablename out c:\DT.txt -c -Sservername -Usa -Ppassword'
或
EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword'导出到TXT文本,用逗号分开
exec master..xp_cmdshell 'bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password'
BULK INSERT 库名..表名
FROM 'c:\test.txt'
WITH (
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
)
如果有10个文件如1.txt,2.txt .... 10.txt,将在数据库中添加10条记录。或将数据库中的image的记录写成一个个的文本文件。
EXEC master..xp_cmdshell 'bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword'
在把表的内容插入到image段,
copied into SQL Server, replacing the existing text or image value. If
direction is OUT (/O) then the text or image value is copied from
SQL Server into the specified 'file', replacing any existing file.TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W"where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?] /S sqlserver The SQL Server to connect to. If 'sqlserver' is n
specified, the local SQL Server is used.
/U login The login to connect with. If 'login' is not spec
a trusted connection will be used.
/P password The password for 'login'. If 'password' is not
specified, a NULL password will be used.
/D database The database that contains the table with the tex
image data. If 'database' is not specified, the d
database of 'login' is used.
/T table The table that contains the text or image value.
/C column The text or image column of 'table'.
/W "where clause" A complete where clause (including the WHERE keyw
that specifies a single row of 'table'.
/F file The file name.
/I Copy text or image value into SQL Server from 'fi
/O Copy text or image value out of SQL Server into '
/K chunksize Size of the data transfer buffer in bytes. Minimu
value is 1024 bytes, default value is 4096 bytes.
/Z Display debug information while running.
/? Display this usage information and exit.You will be prompted for any required options you did not specify.
/*--利用 textcopy.exe文件实现在数据库中存储/读取文件 需要textcopy.exe文件,该文件可以在sql 7.0安装光盘找到--转贴自大力--*/--建立存取处理的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_textcopy]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_textcopy]
GOCREATE PROCEDURE sp_textcopy
@srvname varchar (30), --服务器名
@login varchar (30), --用户名
@password varchar (30), --密码
@dbname varchar (30), --数据库名
@tbname varchar (30), --表名
@colname varchar (30), --存储文件的字段名
@filename varchar (30), --要存取的文件名
@whereclause varchar (40), --条件
@direction char(1) --I 为存储到数据库,O 从数据库中读出成文件
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'textcopy /S"' + @srvname +
'" /U ' + @login +
' /P ' + @password +
' /D ' + @dbname +
' /T ' + @tbname +
' /C ' + @colname +
' /W "' + @whereclause +
'" /F "' + @filename +
'" /' + @direction
EXEC master..xp_cmdshell @exec_str
go--调用示例:
--1.创建示例表和初始化数据
create table tb(id int,img image)
insert tb
select 1,0x
union all select 2,0x
go--2.将文件保存到数据库中
declare @srv varchar(255),@db sysname,@tb sysname,@col sysname
select @srv=@@servername --服务器名
,@db=db_name() --数据库名
,@tb='tb' --表名
,@col='img' --列名
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.jpg','where id=1','I' --保存到id=1的记录中,注意条件是:id=1
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.doc','where id=2','I' --保存到id=2的记录中,注意条件是:id=2--3.从数据库中读出保存为文件
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.jpg','where id=1','O' --读出id=1的记录,注意条件是:id=1
sp_textcopy @srv,'sa','sa的密码',@db,@tb,@col,'c:\test.doc','where id=2','O' --读出id=2的记录,注意条件是:id=2--删除测试环境
drop table tb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_textcopy]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_textcopy]