将Table的数据转换成Insert语句（存储过程）

create Procedure [dbo].[sp_TableToInsert]
@tablename varchar(256),
@where varchar(2000)=''
as
begin
declare @sql varchar(max),@sqlValues varchar(max);
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 '''''''' + convert(varchar(25),'+ name +',121)'+ '+'''''''''+' 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'
				when xtype in (36) then 'case when '+ name +' is null then ''NULL'' else ''''''''+cast('+ name + ' as varchar(36))+'''''''' 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;

if len(@where)>0
begin
	set @sql += ' where '+@where;
end;

exec (@sql);

end;
go

使用情况：
1、没有参数时：exec [dbo].[sp_TableToInsert] 'TableName'
2、有参数时：exec [dbo].[sp_TableToInsert] 'TableName','FuniMainId in(select Id from TableName2 where FuniMenuId in(''3046D747-92EA-464E-8671-D015D664046B''))'