根据表中数据生成insert语句的存储过程

Go语言实现SQL Insert语句生成存储过程

最新推荐文章于 2024-03-08 16:54:41 发布

原创最新推荐文章于 2024-03-08 16:54:41 发布 · 810 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #存储 #null #sql #object #go

SQL Server 专栏收录该内容

3 篇文章

订阅专栏

博客给出了一个用Go语言实现的存储过程，用于生成SQL的Insert语句。该存储过程考虑了标识种子列的插入情况，针对不同的数据类型对值进行处理，当值为null时进行特殊处理，最终生成完整的Insert语句并执行。

有个缺点……就是标识种子的列也insert了

CREATE proc spGenInsertSQL (@tablename varchar(256))

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)

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)

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'

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

--print @sql

exec (@sql)

end