如何写一个语句将一个文本文件的内容，全部插入一个text字段中

[liuxw_xxzx]

表名allfile


字段 filename,content

谢谢


将d:\test.txt的文件内容全部插入到content字段中

[--小F--]

/** 导入文本文件 */
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' 
) 

[cd731107]

先利用导入导出的功能导入d:\test.txt到数据库的临时表，
再将些临时表的数据update到正式表的content字段中

[叶子]

/** 导入文本文件 
EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword’ 

[打一壶酱油]

if object_Id( 'dbsp_bcp_in') Is Not Null
 drop procedure dbsp_bcp_in
go

CREATE procedure dbsp_bcp_in
 @tbl varchar( 40) = NULL,
 @db varchar( 30) = NULL,
 @path varchar( 200) = 'c:\'
as
/*
*************************************************************
Name: dbsp_bcp_in
Description:
   BCPs in an entire table from a local file on the server. The
user passes in the name of a table or view, the database in which
this is located and (optionally) the path where the text file is
located. The file must by in the format: <table>.txt

Usage: exec dbsp_bcp_in <tbl>, <db>, <path>
 exec dbsp_bcp_in 'member', 'iqd_dev', 'c:\mssql'

Author: Steve Jones (7-26-1999)
Copyright: 1999 dkRanch.net

Input Params:
-------------
@tbl  Name of a table or view to bcp out of the server
@db  Name of the database in which the table is located.
@path  path in which to write output file.

Output Params:
--------------

Return: 0 if no error.

Results:
---------

Calls: master..xp_cmdshell

Locals:
--------
@err  Holds error value
@cmd          Holds BCP command 

Modifications:
--------------

*************************************************************
*/
set nocount on
declare @err int,
  @cmd varchar( 250)

select @err = 0
/*
Check parameters and exit if not correct.
*/
if @tbl Is NULL
 select @err = -1
if @db Is NULL
 select @err = -1
if @err = -1
 begin
  Raiserror( 'Parameter Error:Usage:exec dbsp_bcp_in <tbl>, <path>', 12, 1)
  return @err
 end
if ( select count(*) from sysobjects where name = @tbl) = 0  
 begin   
  Raiserror( 'Table does not exist;Please pass in the name of an existing table.', 12, 1)
  return  -1
 end  
if ( select count(*) from master..sysdatabases where name = @db) = 0  
 begin   
  Raiserror( 'Database does not exist;Please pass in the name of an existing database.', 12, 1)
  return  -1
 end  


/*    Ensure there is a backslash on the path */
if right( rtrim( @path), 1) != '\'
  select @path = rtrim( @path) + '\'  


/*
Build the BCP command string
*/
select @cmd = 'bcp ' + rtrim( @db) + '..' + rtrim( @tbl) + ' in ' + rtrim( @path) + rtrim( @tbl) + '.txt -n -S' + @@servername + ' -U sa -T -E'  
select @cmd = 'master..xp_cmdshell ''' + rtrim( @cmd) + ''''

/*
Run the BCP command to move the data out.
*/
exec( @cmd)  

return @err
GO
if object_id( 'dbsp_bcp_in') Is Null
 select 'dbsp_bcp_in Not Created'
else
 select 'dbsp_bcp_in Created'
go

  exec dbsp_bcp_in 'test','Northwind','C:\'