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

最新推荐文章于 2020-12-01 17:54:01 发布

dengchun8720

最新推荐文章于 2020-12-01 17:54:01 发布

阅读量140

点赞数

原文链接：http://www.cnblogs.com/Gabriel-CC/p/7249468.html

版权

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''))'

转载于:https://www.cnblogs.com/Gabriel-CC/p/7249468.html