T-SQL 存储过程: (修订版) 根据基本表结构及其数据生成 INSERT INTO ... 的 SQL

根据基本表结构及其数据生成 INSERT INTO ... SQL 的 T-SQL 存储过程
,在网上能够搜索出的版本主要有两个:
1. CSDN Sky_blue 所作: proc_insert   (可 google)
2. CSDN playyuer 所作: spGenInsertSQL   (可 google)
但这两个版本的程序都曾收录到:
http://www.cnblogs.com/kasafuma/articles/109922.html
但这两个版本程序都有局限性:
如果字段太多或字段值的内容太多而无法生成完整正确的 insert into ... 的 SQL!
例如:

use  Northwind
proc_insert  ' employees '
spGenInsertSQL  ' employees '

执行后均得不到完整正确的 SQL!
其实存储过程本身的代码应该是没有错误的
,只是因为字段太多或字段值的内容太多,varchar 变量容量不够大!
,应该算 Microsoft SQL Server 的缺陷
这个问题已经存在了很久了,长达至少两年多了!
今天终于被窝想到了解决办法,其实很简单:

这两个版本程序生成的 insert sql 都只用了一个字段(变量)
只要多用几个字段即可:
原来是: select f2+f2+f3+ ... +fn
现改为: select f2,f2,f3, ... ,fn
即可!

1.新版本,降低 "字段数量或字段值内容太多" 的影响 而尽量生成正确完整的 INSERT INTO ... SQL:

alter   procedure  Z_SP_GenInsertSQL
(
     @TableName   varchar ( 256 )
    , @AllTopClause   varchar ( 1000 )  =   ''
    , @WhereOrderByClause   varchar ( 1000 )  =   ''   -- 'where 1 = 1 order by null'
)
as
begin
/**/ /*
usage:
Z_SP_GenInsertSQL 'employees','all top 30 PERCENT with ties ','where [LastName] is not null order by employeeid desc'
*/
     declare   @sql   varchar ( 8000 )
     declare   @sqlValues   varchar ( 8000 )
     set   @sql   =   '   '' ( '''   +   char ( 13 )  +   ' , '
     set   @sqlValues   =   '  values ( ''' +   char ( 13 )  +   ' , '
     select   @sqlValues   =   @sqlValues   +  cols  +   '  +  '' , '   +   ''''   +   char ( 13 )  +   ' , '
            , @sql   =   @sql   +   ''' [ '   +  name  +   ' ], '''   +   char ( 13 )  +   ' , '
     from
    (
         select
             case
                 when  xtype  in  ( 48 , 52 , 56 , 59 , 60 , 62 , 104 , 106 , 108 , 122 , 127 )
                     then   ' case when  '   +  name  +   '  is null then  '' NULL ''  else  '   +   ' cast( '   +  name  +   '  as varchar) '   +   '  end '
                 when  xtype  in  ( 58 , 61 )
                     then   ' case when  '   +  name  +   '  is null then  '' NULL ''  else  '   +   '''''''''  +  '   +   ' cast( '   +  name  +   '  as varchar) '   +   ' + '''''''''   +   '  end '
                 when  xtype  in  ( 167 , 175 )
                     then   ' case when  '   +  name  +   '  is null then  '' NULL ''  else  '   +   '''''''''  +  '   +   ' replace( '   +  name  +   ' , '''''''' , '''''''''''' ) '   +   '  +  '''''''''   +   '  end '
                 when  xtype  in  ( 231 , 239 )
                     then   ' case when  '   +  name  +   '  is null then  '' NULL ''  else  '   +   ''' N ''''''  +  '   +   ' replace( '   +  name  +   ' , '''''''' , '''''''''''' ) '   +   '  +  '''''''''   +   '  end '
                 else   ''' NULL '''
             end   as  Cols
            ,name
         from  syscolumns
         where  id  =   object_id ( @TableName )
                 -- and autoval is null --忽略自增整型字段
    ) T
     set   @sql   =   ' select  '   +   @AllTopClause   +   char ( 13 )  +   ''' INSERT INTO  '''   +   char ( 13 )  +   ' , '
                  +   ''' [ ' +   @TableName   +   ' ] '''   +   char ( 13 )  +   ' , '
                  +   left ( @sql , len ( @sql ) - 4 )  +   ''''   +   char ( 13 )  +   ' , '' ) '   +   left ( @sqlValues , len ( @sqlValues ) - 7 )  +   ' , '' ) '''
                  +   char ( 13 )  +   ' from [ '   +   @TableName   +   ' ] '
                  +   char ( 13 )  +   @WhereOrderByClause
     -- select @sql -- select SQL 被截断
     print   @sql   --  print SQL 是完整正确的
     exec  ( @sql )
/**/ /*
select *
from syscolumns    
where id = object_id('test') and autoval is null
*/
end

2.老版本 如果字段太多或字段值的内容太多而无法生成完整正确的 insert into ... 的 SQL

create    proc  Z_SP_GenInsertSQL ( @tablename   varchar ( 256 ))
as
begin
   declare   @sql   varchar ( 8000 )
   declare   @sqlValues   varchar ( 8000 )
   set   @sql   = '  ( '   +   char ( 9 )
   set   @sqlValues   =   ' values  ' +   char ( 9 )  +   ' ( '   +   char ( 9 )  +   ''' + '
   select   @sqlValues   =   @sqlValues   +  cols  +   '  +  '' , '   +   char ( 9 )  +   '''  +  '   , @sql   =   @sql   +   ' [ '   +  name  +   ' ], '   +   CHAR ( 9 )
     from  
        ( select   case  
                   when  xtype  in  ( 48 , 52 , 56 , 59 , 60 , 62 , 104 , 106 , 108 , 122 , 127 )                                
                        then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  '   +   ' cast( ' +  name  +   '  as varchar) ' + '  end '
                   when  xtype  in  ( 58 , 61 )
                        then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + '''''''''  +  '   +   ' cast( ' +  name  + '  as varchar) ' +   ' + ''''''''' + '  end '
                   when  xtype  in  ( 167 , 175 )
                        then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + '''''''''  +  '   +   ' replace( ' +  name + ' , '''''''' , '''''''''''' ) '   +   ' + '''''''''   +   '  end '
                   when  xtype  in  ( 231 , 239 )
                        then   ' case when  ' +  name  + '  is null then  '' NULL ''  else  ' + ''' N ''''''  +  '   +   ' replace( ' +  name + ' , '''''''' , '''''''''''' ) '   +   ' + '''''''''   +   '  end '
                   else   ''' NULL '''
                 end   as  Cols,name
            from  syscolumns  
           where  id  =   object_id ( @tablename )  and  autoval  is   null
        ) T 
   set   @sql   = ' select  '' INSERT INTO  '   +   CHAR ( 9 )  +   ' [ ' +   @tablename   +   ' ] '   +   CHAR ( 9 )  +   left ( @sql , len ( @sql ) - 2 )  +   char ( 9 )  +   ' )  '    +   CHAR ( 9 )  +   left ( @sqlValues , len ( @sqlValues ) - 5 )  +   char ( 9 )  +   ' ) ''  from  ' + @tablename
   print   @sql
   exec  ( @sql )
/**/ /**/ /**/ /*
select *
from syscolumns  
where id = object_id('test') and autoval is null
*/
end

3. .Net/C# 版本请看:
http://www.cnblogs.com/Microshaoft/archive/2005/07/19/195752.html

转载于:https://www.cnblogs.com/Microshaoft/archive/2005/08/06/208876.html