将表中数据转换成Insert语句

/****** Object:  StoredProcedure [dbo].[spGenInsertSQL]    Script Date: 02/01/2012 10:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER   procedure [dbo].[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 
                 -- select * from systypes 来查看各类型对应号码
                  when xtype in (48,52,56,59,60,62,104,106,108,122,127)   --Int,Real                             
                       then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
                  when xtype in (58,61)  --datetime,samlldatetime
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
                 when xtype in (167) --varchar
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (231)--nvarchar
                       then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
                  when xtype in (175) --char
                       then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'
                  when xtype in (239) --nchar
                       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 autoval is null and  id  =  object_id(@tablename)  and  xtype  <>189  and  xtype  <>34  and  xtype  <>35  and    xtype  <>36  
        ) T 
  set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
  --print @sql
  exec (@sql)
end