存储过程结合bcp--将数据库记录导出成SQL脚本的形式。_Sql Server_学习笔记★闵涛★计算机学习电脑编程软硬件技巧


	转至繁体中文版	\| 网站首页 \| 图文教程 \| 资源下载 \| 站长博客 \| 图片素材 \| 武汉seo \| 武汉网站优化 \|

存储过程结合bcp--将数据库记录导出成SQL脚本的形式。

作者：闵涛文章来源：闵涛的学习笔记点击数：2729 更新时间：2007/11/14 12:58:48

           when a.xtype =48    then ''''case when ''''+a.name+'''' is null then ''''''''NULL'''''''' else ''''+''''convert(varchar(6),''''+a.name +'''')''''+'''' end''''
--            when a.xtype =165 then ''''case when ''''+a.name+'''' is null then ''''''''NULL'''''''' else ''''+''''convert(varchar(''''+convert(varchar(4),a.length*2+2)+''''),''''+a.name +'''')''''+'''' end''''
           when a.xtype =167 then ''''case when ''''+a.name+'''' is null then ''''''''NULL'''''''' else ''''+''''''''''''''''''''''''''''''''''''+''''+''''replace(''''+a.name+'''','''''''''''''''''''''''''''''''','''''''''''''''''''''''''''''''''''''''''''''''')'''' + ''''+''''''''''''''''''''''''''''''''''''+'''' end''''
           else ''''''''''''NULL''''''''''''
           end as col,a.colid,a.name
           from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and    a.xtype <>36
           )t order by colid

Declare @sqlstr_B varchar(8000)
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+'''') ''''+left(@sqlstr1,len(@sqlstr1)-3)+'''')'''''''' ,''''''''''''+@tablename+'''''''''''' from ''''+@tablename

--select @sqlstr=@sqlstr+ '''' where CATLRID=''''''''''''+@str_bookid +''''''''''''''''
select @sqlstr=@sqlstr+ '''' where ''''+@ls_colName+ @ls_operator+''''''''''''''''+@str_bookid +''''''''''''''''
--
--print @sqlstr

select @sqlstr_B=''''insert into A_Imp_Insert_Sql(insertsql,flag) (''''+@sqlstr+'''')''''
--print @sqlstr_B

--exec( @sqlstr)
exec( @sqlstr_B)
--导出为单独的存储过程
--Declare @bcpstr varchar(1000)
--set @bcpstr = ''''bcp bookonline_test..A_Imp_Insert_Sql''''
--set @bcpstr =@bcpstr +'''' out ''''+''''c:\''''+@tablename+''''.TXT''''+ '''' -c -S''''+@ls_server+'''' -U''''+@ls_user+'''' -P''''+@ls_pwd+''''''''
--EXEC master..xp_cmdshell @bcpstr

set nocount off
end
GO

建存储过程proc_imp_text

CREATE PROCEDURE proc_imp_text (@ls_dbname varchar(50),@ls_server varchar(200),@ls_user varchar(200),@ls_pwd varchar(200),@ls_filepathname varchar(500),@ls_tabname varchar(50))
AS
Declare @bcpstr varchar(1500)
begin

set @bcpstr = ''''bcp "select insertsql from ''''+@ls_dbname+''''..A_Imp_Insert_Sql where flag=''''''''''''+@ls_tabname+''''''''''''" queryout ''''+@ls_filepathname+ '''' -w -S''''+@ls_server+'''' -U''''+@ls_user+'''' -P''''+@ls_pwd+''''''''

EXEC master..xp_cmdshell @bcpstr
end

GO
不明处联系：gutengcom@hotmail.com

上一页 [1] [2]