CREATE procedure Z_SP_GenInsertSQL
(
@TableName varchar(256)
,@AllTopClause varchar(1000) = ' '
,@WhereOrderByClause varchar(1000) = ' ' -- 'where 1 = 1 order by null '
)
as
begin
/**//*
usage:
Z_SP_GenInsertSQL 'employees ', 'all top 30 PERCENT with ties ', 'where [LastName] is not null order by employeeid desc '
*/
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql = ' ' '( ' ' ' + char(13) + ', '
set @sqlValues = ' values ( ' ' '+ char(13) + ', '
select @sqlValues = @sqlValues + cols + ' + ' ', ' + ' ' ' ' + char(13) + ', '
,@sql = @sql + ' ' '[ ' + name + '], ' ' ' + char(13) + ', '
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 ' + @AllTopClause + char(13) + ' ' 'INSERT INTO ' ' ' + char(13) + ', '
+ ' ' '[ '+ @TableName + '] ' ' ' + char(13) + ', '
+ left(@sql,len(@sql)-4) + ' ' ' ' + char(13) + ', ' ') ' + left(@sqlValues,len(@sqlValues)-7) + ', ' ') ' ' '
+ char(13) + 'from [ ' + @TableName + '] '
+ char(13) + @WhereOrderByClause
--select @sql -- select SQL 被截断
print @sql -- print SQL 是完整正确的
exec (@sql)
/**//*
select *
from syscolumns
where id = object_id( 'test ') and autoval is null
*/
end
GO
CREATE procedure Z_SP_GenInsertSQL
(
@TableName varchar(256)
,@AllTopClause varchar(1000) = ' '
,@WhereOrderByClause varchar(1000) = ' ' -- 'where 1 = 1 order by null '
)
as
begin
/**//*
usage:
Z_SP_GenInsertSQL 'employees ', 'all top 30 PERCENT with ties ', 'where [LastName] is not null order by employeeid desc '
*/
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql = ' ' '( ' ' ' + char(13) + ', '
set @sqlValues = ' values ( ' ' '+ char(13) + ', '
select @sqlValues = @sqlValues + cols + ' + ' ', ' + ' ' ' ' + char(13) + ', '
,@sql = @sql + ' ' '[ ' + name + '], ' ' ' + char(13) + ', '
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 ' + @AllTopClause + char(13) + ' ' 'INSERT INTO ' ' ' + char(13) + ', '
+ ' ' '[ '+ @TableName + '] ' ' ' + char(13) + ', '
+ left(@sql,len(@sql)-4) + ' ' ' ' + char(13) + ', ' ') ' + left(@sqlValues,len(@sqlValues)-7) + ', ' ') ' ' '
+ char(13) + 'from [ ' + @TableName + '] '
+ char(13) + @WhereOrderByClause
--select @sql -- select SQL 被截断
print @sql -- print SQL 是完整正确的
exec (@sql)
/**//*
select *
from syscolumns
where id = object_id( 'test ') and autoval is null
*/
end
GO
在查询分析器中运行下面的语句进行调用:
Z_SP_GenInsertSQL 'UserInfo ', ' ', ' '