存储过程结合bcp--将数据库记录导出成SQL脚本的形式。
pb9+SQLserver2k下通过
先建表A_Imp_Insert_Sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[A_Imp_Insert_Sql]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[A_Imp_Insert_Sql]
GO
CREATE TABLE [dbo].[A_Imp_Insert_Sql] (
[insertsql] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[flag] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
建存储过程proc_insert
CREATE proc proc_insert (@ls_server varchar(256),@ls_user varchar(20),@ls_pwd varchar(20),@tablename varchar(30),@ls_colName varchar(20),@ls_operator varchar(10),@str_bookid varchar(100))
as
begin
set nocount on
declare @sqlstr varchar(8000)
declare @sqlstr1 varchar(8000)
declare @sqlstr2 varchar(8000)
--改为在pb中删除表.
--exec( 'truncate table A_Imp_Insert_Sql')
--insert into A_Imp_Insert_Sql
--A_CLS_Temp
if @tablename ='A_CLS_Temp'
begin
-- 单独处理了，特殊对待
select @sqlstr=' select ''insert ChineseLibraryClass '
end
else
begin
select @sqlstr=' select ''insert '+@tablename
end
-- select @sqlstr=' select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 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 =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
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