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

from :Microshaoft (大舌头)
根据基本表结构及其数据生成 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

posted on 2005-08-06 14:26 Microshaoft 阅读(1072) 评论(12)  编辑 收藏 收藏至365Key

评论

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

非常不错，收藏。

2005-08-06 15:05 | 生活、工作

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

呵呵，

2005-08-06 15:16 | 蛙蛙池塘

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

支持二进制字段么？

2005-08-06 19:02 | progame

# 二进制字段 无法支持   

二进制字段 无法支持 字段值只是用 null 代替

2005-08-06 19:30 | Microshaoft

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

咦，灵感前两天也在做这个....

2005-08-07 02:51 | 寒枫天伤

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

我在用 isql 工具来执行这个存储过程时，得到的结果同样是没一行的数据都被截断了。
这是结果中的一行
==============
INSERT INTO [employees] ([EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country],[HomePhone],[Extension],[Photo],[Notes],[ReportsTo],[PhotoPath]) values (1,N'Davolio',N'Nancy',N

==============
这行总共是 258 字节（包括前后各有一个空格）。

2005-08-08 15:52 | ade

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

上面打错了，应该是：
---------------------
得到的结果同样是每一行的数据都被截断了。
-------------------

2005-08-08 15:53 | ade

# use QA   

use 查询分析器

2005-08-08 16:34 | Microshaoft

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

因为我要把这段功能放到一个 bat 文件中，所以我必须要使用脚本工具。

2005-08-08 17:52 | ade

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

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>isql -S.\psqlke
-Usa -P1234 -dnorthwind -Q"exec z_sp_geninsertsql 'employees'" -oc:\test
.sql -x80000

好像的确不幸!

你可以自己写一段 命令行 程序:
参阅:
http://microshaoft.cnblogs.com/archive/2005/07/19/195752.html

2005-08-08 20:45 | Microshaoft

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

也没有解决字符类型足8000(4000),且里面包含'时问题.
--测试
select datalength(''''+replace(a,'''','''''')+'''') as a
from(select replicate('''',8000) as a)a

2005-08-10 07:52 | zjcxc

# 3q for your reminder