//在SQL Server的存储过程中实现对文本的操作
如,对一个文本文件c:\ttt.txt;
怎么实现从文本中读取一行,怎么实现写入一行呢?
谢谢。
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
Declare @tmp int
declare @msg varchar(3000)
SET @msg='Hello. MS SQL Server 2000. I Love you!!!' --這字串將會被寫到SQL Server的安裝目錄下的Nipsan.Txt文件里面
/* 取得SQL Server的安裝路徑*/
declare @strPath nvarchar(512)
Exec sp_MSGet_Setup_paths @strPath OUTPUT
Set @strPath=@strPath+'\Nipsan.Txt' --組成文件名
--創建Scripting組件實例
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
--創建文件
EXEC @hr = sp_OAMethod @object, 'CreateTextFile', @tmp OUTPUT , @strPath
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
--把@msg寫到文件里面去
EXEC @hr = sp_OAMethod @tmp, 'Write',NULL, @msg
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
--關閉文件
EXEC @hr = sp_OAMethod @tmp, 'Close',NULL
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
-----------寫文件操作Demo完成---------------------------------------------------------------
--打開文件
EXEC @hr = sp_OAMethod @object, 'OpenTextFile', @tmp OUTPUT ,@strPath
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
SET @msg=''
--讀文件
EXEC @hr = sp_OAMethod @tmp, 'Read', @msg OUT,3000
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object
RETURN
END
-----讀文件操作Demo完成
SELECT @msg AS RESULT
(
@au_dbname varchar(50),
@au_txtpath nvarchar(100),
@au_splitch varchar(10),
@au_row varchar(10)
)
As
begin
declare @str varchar(300)
declare @str1 varchar(300)
set @str='delete from '+@au_dbname
--删除
set @str1='bulk insert sjzx..'+@au_dbname+' from '''+@au_txtpath+'''
with(FIELDTERMINATOR = '''+@au_splitch+''',ROWTERMINATOR = '''+@au_row+''')'
exec (@str)
exec (@str1)
end
执行
use sjzx
go
exec Txt_insert_tmp 'tqhm_tmp','G:\SJZX\数据中心\200604.txt',',','
'
go
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'