文章详情

  • 游戏榜单
  • 软件榜单
关闭导航
热搜榜
热门下载
热门标签
php爱好者> php文档>根据表中记录生成insert into语句的存储过程

根据表中记录生成insert into语句的存储过程

时间:2011-04-09  来源:zwkuang

根据网上的参考,做了些修改,主要是增加了能够按条件生成和按ID号顺序排序

IF OBJECT_ID('spGenInsertSQL','P') IS NOT NULL
DROP PROC spGenInsertSQL
GO
CREATE   proc spGenInsertSQL (@tablename varchar(256),@cond 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)  --如果是数值型或MOENY型    

                     then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'

                when xtype in (58,61) --如果是datetime或smalldatetime类型

                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'convert(varchar,'+ name +',120)'+ '+'''''''''+' 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 id = object_id(@tablename)

      ) T

--下面红色为修改过的内容
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename+' where AntiCode like '''+@cond+'%'' order by ID asc'
--print @sql
exec (@sql)
end
GO

相关阅读 更多 +
排行榜 更多 +
瓢虫少女

瓢虫少女

飞行射击 下载
潜艇鱼雷

潜艇鱼雷

飞行射击 下载
网络掠夺者

网络掠夺者

飞行射击 下载