SQL Server存储过程生成insert语句实例
你肯定有过这样的烦恼,同样的表,不同的数据库,加入你不能执行select insert
那么你肯定需要一条这样的存储过程,之需要传入表明,就会给你生成数据的插入语句。
当然数据表数量太大,你将最好用别的方式
create proc [dbo].[spgeninsertsql] (@tablename 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)
then 'case when '+ name +' is null then ''null'' else ' + 'cast('+ name + ' as varchar)'+' end'
when xtype in (58,61)
--then '''''''''+convert(char(23),'+name+',121)+''''''''' --datetime
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
sql语句
最后的结果:
insert into [syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) values ('0002ca83-af2f-4d8f-a345-33ca1cc7cf3c','任务调度系统',18,'2013-01-02 21:42:30.013','',null,'2013-01-02 21:42:30.013')
insert into [syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) values ('0004a6f3-ec28-4d1f-ba40-0fc4b2218c92','任务调度系统',18,'2013-07-09 19:36:00.060','',null,'2013-07-09 19:36:00.060')
insert into [syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) values ('00094d35-7b51-4ea3-871e-ce17e293b157','任务调度系统',18,'2013-05-16 15:21:20.070','',null,'2013-05-16 15:21:20.070')
insert into [syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) values ('000bfbb0-b37d-4d6e-9fa2-3069d4f18f84','任务调度系统',18,'2013-04-11 11:41:50.030','',null,'2013-04-11 11:41:50.030')
insert into [syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) values ('000c2cbc-e358-4469-bc2c-04f4ddcd72cd','任务调度系统',18,'2013-05-06 16:07:00.037','',null,'2013-05-06 16:07:00.037')
insert into [syssample] ([id],[name],[age],[bir],[photo],[note],[createtime]) values ('000cb795-40ec-4783-b7a4-8d298df63b70','任务调度系统',18,'2013-01-23 20:52:30.030','',null,'2013-01-23 20:52:30.030')
上一篇: SQL字符串处理函数大全
推荐阅读
-
SQL Server存储过程生成insert语句实例
-
在SQL Server中使用SQL语句查询一个存储过程被其它所有的存储过程引用的存储过程名
-
Sql Server中存储过程中输入和输出参数(简单实例 一看就懂)
-
SQL Server中使用Trigger监控存储过程更改脚本实例
-
SQL Server存储过程生成insert语句实例
-
如何在SQL SERVER 2005存储过程中,使用循环语句
-
SQL SERVER 查看所有存储过程或视图里 包含某个关键字的查询语句
-
如何在SQL Server 2008下轻松调试T-SQL语句和存储过程
-
SQL Server中使用Trigger监控存储过程更改脚本实例
-
oracle存储过程动态sql语句实例讲解