转:邹建老师--SQL Server数据导出

 转:http://topic.csdn.net/t/20050414/22/3937592.html

 

if     exists    ( select     *     from    dbo.sysobjects    where    id    =     object_id (N ' [dbo].[p_exporttb] ' )    and     OBJECTPROPERTY (id,   N ' IsProcedure ' )    =     1 )   
   drop     procedure     [ dbo ] . [ p_exporttb ]    
   GO    
    
   /**/ /*--数据导出dBase   
    
  导出查询语句中的数据到dBase,如果文件不存在,将自动创建文件   
  基于通用性考虑,仅支持导出标准数据类型   
  --邹建   2003.10--*/    
    
   /**/ /*--调用示例   
    
  --导出dBase   
  p_exporttb   @sqlstr='select   *   from   地区资料',@path='c:',@over=1   
  --*/    
   create     proc    p_exporttb   
   @sqlstr     varchar ( 8000 ),  -- 要导出的查询名   
   @path     nvarchar ( 1000 ),  -- 文件存放目录   
   @fname     nvarchar ( 250 ) = ' temp.dbf ' , -- 文件名,默认为temp   
   @over     bit = 0   -- 是否覆盖已经存在的文件,如果不覆盖,则直接追加   
   as    
   declare     @err     int , @src     nvarchar ( 255 ), @desc     nvarchar ( 255 ), @out     int    
   declare     @obj     int , @constr     nvarchar ( 1000 ), @sql     varchar ( 8000 ), @fdlist     varchar ( 8000 )   
    
   -- 参数检测   
   if     isnull ( @fname , '' ) = ''     set     @fname = ' temp.dbf '    
    
   -- 检查文件是否已经存在   
   if     right ( @path , 1 ) <> ' '     set     @path = @path + ' '    
   create     table    #tb(a    bit ,b    bit ,c    bit )   
   set     @sql = @path + @fname    
   insert     into    #tb    exec    master..xp_fileexist    @sql    
   if     exists ( select     1     from    #tb    where    a = 1 )   
   if     @over = 1    
   begin    
   set     @sql = ' del    ' + @sql    
   exec    master..xp_cmdshell    @sql ,no_output   
   end    
   else    
   set     @over = 0    
   else    
   set     @over = 1    
    
   -- 数据库创建语句   
   set     @sql = @path + @fname    
   set     @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Extended   Properties="dBASE   5.0; '    
   + ' ;HDR=NO;DATABASE= ' + @path + ' " '    
    
   -- 创建表的SQL   
   declare     @tbname    sysname   
   set     @tbname = ' ##tmp_ ' + convert ( varchar ( 38 ), newid ())   
   set     @sql = ' select   *   into   [ ' + @tbname + ' ]   from( ' + @sqlstr + ' )   a '    
   exec ( @sql )   
    
   -- 连接数据库   
   exec     @err = sp_oacreate    ' adodb.connection ' , @obj    out   
   if     @err <> 0     goto    lberr   
    
   exec     @err = sp_oamethod    @obj , ' open ' , null , @constr    
   if     @err <> 0     goto    lberr   
    
   -- 创建表的SQL   
   select     @sql = '' , @fdlist = ''    
   select     @fdlist = @fdlist + ' , ' + a.name   
  , @sql = @sql + ' ,[ ' + a.name + ' ]    '    
   + case     when    b.name    in ( ' char ' , ' nchar ' , ' varchar ' , ' nvarchar ' )    then    
     ' text( ' + cast ( case     when    a.length > 250     then     250     else    a.length    end     as     varchar ) + ' ) '    
   when    b.name    in ( ' tynyint ' , ' int ' , ' bigint ' , ' tinyint ' )    then     ' int '    
   when    b.name    in ( ' smalldatetime ' , ' datetime ' )    then     ' datetime '    
   when    b.name    in ( ' money ' , ' smallmoney ' )    then     ' money '    
   else    b.name    end    
   FROM    tempdb..syscolumns   a    left     join    tempdb..systypes   b    on    a.xtype = b.xusertype   
   where    b.name    not     in ( ' image ' , ' text ' , ' uniqueidentifier ' , ' sql_variant ' , ' ntext ' , ' varbinary ' , ' binary ' , ' timestamp ' )   
   and    a.id = ( select    id    from    tempdb..sysobjects    where    name = @tbname )   
   select     @sql = ' create   table   [ ' + @fname    
   + ' ]( ' + substring ( @sql , 2 , 8000 ) + ' ) '    
  , @fdlist = substring ( @fdlist , 2 , 8000 )   
    
   if     @over = 1    
   begin    
   exec     @err = sp_oamethod    @obj , ' execute ' , @out    out, @sql    
   if     @err <> 0     goto    lberr   
   end    
    
   exec     @err = sp_oadestroy    @obj    
    
   set     @sql = ' openrowset( '' MICROSOFT.JET.OLEDB.4.0 '' , '' dBase   5.0;DATABASE= '    
   + @path + ''' , '' select   *   from   [ ' + @fname + ' ] '' ) '    
    
   -- 导入数据   
   exec ( ' insert   into    ' + @sql + ' ( ' + @fdlist + ' )   select    ' + @fdlist + '    from   [ ' + @tbname + ' ] ' )   
    
   set     @sql = ' drop   table   [ ' + @tbname + ' ] '    
   exec ( @sql )   
    
   return    
    
  lberr:   
   exec    sp_oageterrorinfo    0 , @src    out, @desc    out   
  lbexit:   
   select     cast ( @err     as     varbinary ( 4 ))    as    错误号   
  , @src     as    错误源, @desc     as    错误描述   
   select     @sql , @constr , @fdlist    
   go

 

if     exists    ( select     *     from    dbo.sysobjects    where    id    =     object_id (N ' [dbo].[p_exporttb] ' )    and     OBJECTPROPERTY (id,   N ' IsProcedure ' )    =     1 )   
   drop     procedure     [ dbo ] . [ p_exporttb ]    
   GO    
    
   /**/ /*--数据导出EXCEL   
    
  导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件   
  如果文件不存在,将自动创建文件   
  如果表不存在,将自动创建表   
  基于通用性考虑,仅支持导出标准数据类型   
    
  --邹建   2003.10(引用请保留此信息)--*/    
    
   /**/ /*--调用示例   
    
  p_exporttb   @sqlstr='select   *   from   地区资料'   
  ,@path='c:',@fname='aa.xls',@sheetname='地区资料'   
  --*/    
   create     proc    p_exporttb   
   @sqlstr     varchar ( 8000 ),  -- 查询语句,如果查询语句中使用了order   by   ,请加上top   100   percent   
   @path     nvarchar ( 1000 ),  -- 文件存放目录   
   @fname     nvarchar ( 250 ),  -- 文件名   
   @sheetname     varchar ( 250 ) = ''   -- 要创建的工作表名,默认为文件名   
   as      
   declare     @err     int , @src     nvarchar ( 255 ), @desc     nvarchar ( 255 ), @out     int    
   declare     @obj     int , @constr     nvarchar ( 1000 ), @sql     varchar ( 8000 ), @fdlist     varchar ( 8000 )   
    
   -- 参数检测   
   if     isnull ( @fname , '' ) = ''   set     @fname = ' temp.xls '    
   if     isnull ( @sheetname , '' ) = ''     set     @sheetname = replace ( @fname , ' . ' , ' # ' )   
    
   -- 检查文件是否已经存在   
   if     right ( @path , 1 ) <> ' '     set     @path = @path + ' '    
   create     table    #tb(a    bit ,b    bit ,c    bit )   
   set     @sql = @path + @fname    
   insert     into    #tb    exec    master..xp_fileexist    @sql    
    
   -- 数据库创建语句   
   set     @sql = @path + @fname    
   if     exists ( select     1     from    #tb    where    a = 1 )   
   set     @constr = ' DRIVER={Microsoft   Excel   Driver   (*.xls)};DSN= '''' ;READONLY=FALSE '    
           + ' ;CREATE_DB=" ' + @sql + ' ";DBQ= ' + @sql    
   else    
   set     @constr = ' Provider=Microsoft.Jet.OLEDB.4.0;Extended   Properties="Excel   8.0;HDR=YES '    
   + ' ;DATABASE= ' + @sql + ' " '    
    
   -- 连接数据库   
   exec     @err = sp_oacreate    ' adodb.connection ' , @obj    out   
   if     @err <> 0     goto    lberr   
    
   exec     @err = sp_oamethod    @obj , ' open ' , null , @constr    
   if     @err <> 0     goto    lberr   
    
   -- 创建表的SQL   
   declare     @tbname    sysname   
   set     @tbname = ' ##tmp_ ' + convert ( varchar ( 38 ), newid ())   
   set     @sql = ' select   *   into   [ ' + @tbname + ' ]   from( ' + @sqlstr + ' )   a '    
   exec ( @sql )   
    
   select     @sql = '' , @fdlist = ''    
   select     @fdlist = @fdlist + ' ,[ ' + a.name + ' ] '    
  , @sql = @sql + ' ,[ ' + a.name + ' ]    '    
   + case      
   when    b.name    like     ' %char '      
   then     case     when    a.length > 255     then     ' memo '    
   else     ' text( ' + cast (a.length    as     varchar ) + ' ) '     end    
   when    b.name    like     ' %int '     or    b.name = ' bit '     then     ' int '    
   when    b.name    like     ' %datetime '     then     ' datetime '    
   when    b.name    like     ' %money '     then     ' money '    
   when    b.name    like     ' %text '     then     ' memo '    
   else    b.name    end    
   FROM    tempdb..syscolumns   a    left     join    tempdb..systypes   b    on    a.xtype = b.xusertype   
   where    b.name    not     in ( ' image ' , ' uniqueidentifier ' , ' sql_variant ' , ' varbinary ' , ' binary ' , ' timestamp ' )   
   and    a.id = ( select    id    from    tempdb..sysobjects    where    name = @tbname )   
    
   if     @@rowcount = 0     return    
    
   select     @sql = ' create   table   [ ' + @sheetname    
   + ' ]( ' + substring ( @sql , 2 , 8000 ) + ' ) '    
  , @fdlist = substring ( @fdlist , 2 , 8000 )   
    
   exec     @err = sp_oamethod    @obj , ' execute ' , @out    out, @sql    
   if     @err <> 0     goto    lberr   
    
   exec     @err = sp_oadestroy    @obj    
    
   -- 导入数据   
   set     @sql = ' openrowset( '' MICROSOFT.JET.OLEDB.4.0 '' , '' Excel   8.0;HDR=YES   
  ;DATABASE= ' + @path + @fname + ''' ,[ ' + @sheetname + ' $]) '    
    
   exec ( ' insert   into    ' + @sql + ' ( ' + @fdlist + ' )   select    ' + @fdlist + '    from   [ ' + @tbname + ' ] ' )   
    
   set     @sql = ' drop   table   [ ' + @tbname + ' ] '    
   exec ( @sql )   
   return    
    
  lberr:   
   exec    sp_oageterrorinfo    0 , @src    out, @desc    out   
  lbexit:   
   select     cast ( @err     as     varbinary ( 4 ))    as    错误号   
  , @src     as    错误源, @desc     as    错误描述   
   select     @sql , @constr , @fdlist    
   go