如何写一个语句将一个文本文件的内容,全部插入一个text字段中
表名allfile
字段 filename,content
谢谢
将d:\test.txt的文件内容全部插入到content字段中
字段 filename,content
谢谢
将d:\test.txt的文件内容全部插入到content字段中
作者: liuxw_xxzx 发布时间: 2011-06-10
先利用导入导出的功能导入d:\test.txt到数据库的临时表,
再将些临时表的数据update到正式表的content字段中
再将些临时表的数据update到正式表的content字段中
作者: cd731107 发布时间: 2011-06-10
SQL code
/** 导入文本文件 EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword’
作者: maco_wang 发布时间: 2011-06-10
SQL code
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:\'
作者: abcjun188 发布时间: 2011-06-10