if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[TestProc] GOSET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO /*--实现数据导入/导出的存储过程 导出调用示例 exec TestProc 'server','uid','pwd','c:\result.txt','Northwind..Orders',@rowsplit=',' 导入调用示例 exec TestProc 'server','uid','pwd','c:\result.txt','Northwind..Orders',0 --*/create procedure TestProc @servername varchar(200) --服务器名 ,@username varchar(200) --用户名,如果用NT验证方式,则为空'' ,@password varchar(200) --密码 ,@filename varchar(1000) --目录名+文件名 ,@tbname varchar(500)='' --数据库..表名 ,@isout bit=1 --1为导出(默认),0为导入 ,@fdsplit varchar(10)='\t' --字段分隔符,默认为制表符 ,@rowsplit varchar(10)='\n' --记录分隔符,默认为回车符 as declare @sql varchar(8000)set @sql='bcp "'+@tbname +case when @isout=1 then '" out' else '" in' end +' "'+@filename+'" /c' +' /S"'+@servername +case when isnull(@username,'')='' then '' else '" /U"'+@username end +'" /P"'+isnull(@password,'')+'"' +' /t"'+@fdsplit+'"' +' /r"'+@rowsplit+'"'exec master..xp_cmdshell @sql GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
drop procedure [dbo].[TestProc]
GOSET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*--实现数据导入/导出的存储过程
导出调用示例
exec TestProc 'server','uid','pwd','c:\result.txt','Northwind..Orders',@rowsplit=','
导入调用示例
exec TestProc 'server','uid','pwd','c:\result.txt','Northwind..Orders',0
--*/create procedure TestProc
@servername varchar(200) --服务器名
,@username varchar(200) --用户名,如果用NT验证方式,则为空''
,@password varchar(200) --密码
,@filename varchar(1000) --目录名+文件名
,@tbname varchar(500)='' --数据库..表名
,@isout bit=1 --1为导出(默认),0为导入
,@fdsplit varchar(10)='\t' --字段分隔符,默认为制表符
,@rowsplit varchar(10)='\n' --记录分隔符,默认为回车符
as
declare @sql varchar(8000)set @sql='bcp "'+@tbname
+case when @isout=1 then '" out' else '" in' end
+' "'+@filename+'" /c' +' /S"'+@servername
+case when isnull(@username,'')='' then ''
else '" /U"'+@username end
+'" /P"'+isnull(@password,'')+'"'
+' /t"'+@fdsplit+'"'
+' /r"'+@rowsplit+'"'exec master..xp_cmdshell @sql
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名 in f:\1.txt -c -q -t, S"服务器名" -U"用户名" -P"密码"'sql 到TXT
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名 out f:\1.txt -c -q -S"服务器名" -U"用户名" -P"密码"'