根据基本表结构及其数据生成 INSERT ... 的 SQL

create  proc spGenInsertSQL
@TableName as varchar(100)
as
--declare @TableName varchar(100)
--set @TableName = @#orders@#
--set @TableName = @#eeducation@#
DECLARE xCursor CURSOR FOR
SELECT name,xusertype
FROM syscolumns
WHERE (id = OBJECT_ID(@TableName))
declare @F1 varchar(100)
declare @F2 integer
declare @SQL varchar(8000)
set @sql =@#SELECT @#@#INSERT INTO @# + @TableName + @# VALUES(@#@#@#
OPEN xCursor
FETCH xCursor into @F1,@F2
WHILE @@FETCH_STATUS = 0
BEGIN
    set @sql =@sql +
              + case when @F2 IN (35,58,99,167,175,231,239,61) then @# + case when @# + @F1 + @# IS NULL then @#@#@#@# else @#@#@#@#@#@#@#@# end + @#  else @#+@# end
              + @#replace(ISNULL(cast(@# + @F1 + @# as varchar),@#@#NULL@#@#),@#@#@#@#@#@#@#@#,@#@#@#@#@#@#@#@#@#@#@#@#)@#
              + case when @F2 IN (35,58,99,167,175,231,239,61) then @# + case when @# + @F1 + @# IS NULL then @#@#@#@# else @#@#@#@#@#@#@#@# end + @#  else @#+@# end
              + char(13) + @#@#@#,@#@#@#
    FETCH NEXT FROM xCursor into @F1,@F2
END
CLOSE xCursor
DEALLOCATE xCursor
set @sql = left(@sql,len(@sql) - 5) + @# + @#@#)@#@# FROM @# + @TableName
print @sql
exec (@sql)

第二版:2003.03.08

alter proc SPGenInsertSQL (@tablename varchar(256))
as
begin
  declare @sql varchar(8000)
  declare @sqlValues varchar(8000)
  set @sql =@# (@#
  set @sqlValues = @#values (@#@#+@#
  select @sqlValues = @sqlValues + cols + @# + @#@#,@#@# + @# ,@sql = @sql + @#[@# + name + @#],@#
    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)
                       then @#case when @#+ name +@# is null then @#@#NULL@#@# else @#+@#@#@#@#@#@#@#@#@# + @# + @#replace(@#+ name+@#,@#@#@#@#@#@#@#@#,@#@#@#@#@#@#@#@#@#@#@#@#)@# + @#+@#@#@#@#@#@#@#@#@#+@# end@#
                  when xtype in (231)
                       then @#case when @#+ name +@# is null then @#@#NULL@#@# else @#+@#@#@#N@#@#@#@#@#@# + @# + @#replace(@#+ name+@#,@#@#@#@#@#@#@#@#,@#@#@#@#@#@#@#@#@#@#@#@#)@# + @#+@#@#@#@#@#@#@#@#@#+@# end@#
                  when xtype in (175)
                       then @#case when @#+ name +@# is null then @#@#NULL@#@# else @#+@#@#@#@#@#@#@#@#@# + @# + @#cast(replace(@#+ name+@#,@#@#@#@#@#@#@#@#,@#@#@#@#@#@#@#@#@#@#@#@#) as Char(@# + cast(length as varchar)  + @#))+@#@#@#@#@#@#@#@#@#+@# end@#
                  when xtype in (239)
                       then @#case when @#+ name +@# is null then @#@#NULL@#@# else @#+@#@#@#N@#@#@#@#@#@# + @# + @#cast(replace(@#+ name+@#,@#@#@#@#@#@#@#@#,@#@#@#@#@#@#@#@#@#@#@#@#) as Char(@# + cast(length as varchar)  + @#))+@#@#@#@#@#@#@#@#@#+@# end@#
                  else @#@#@#NULL@#@#@#
                end as Cols,name
           from syscolumns 
          where id = object_id(@tablename)
        ) T
  set @sql =@#select @#@#INSERT INTO [@#+ @tablename + @#]@# + left(@sql,len(@sql)-1)+@#) @# + left(@sqlValues,len(@sqlValues)-4) + @#)@#@# from @#+@tablename
  --print @sql
  exec (@sql)
end

第三版: 2003.3.9

ALTER   proc SPGenInsertSQL (@tablename varchar(256))
as
begin
  declare @sql varchar(8000)
  declare @sqlValues varchar(8000)
  set @sql =@# (@#
  set @sqlValues = @#values (@#@#+@#
  select @sqlValues = @sqlValues + cols + @# + @#@#,@#@# + @# ,@sql = @sql + @#[@# + name + @#],@#
    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 [@#+ @tablename + @#]@# + left(@sql,len(@sql)-1)+@#) @# + left(@sqlValues,len(@sqlValues)-4) + @#)@#@# from @#+@tablename
  print @sql
  exec (@sql)
/*
select *
from syscolumns 
where id = object_id(@#test@#) and autoval is null
*/
end

原文转自：http://www.ltesting.net