SQLServer存储过程生成insert语句实例
你肯定有过这样的烦恼,同样的表,不同的数据库,加入你不能执行select insert
那么你肯定需要一条这样的存储过程,之需要传入表明,就会给你生成数据的插入语句。
当然数据表数量太大,你将最好用别的方式
Create proc[dbo].[spGenInsertSQL](@tablenamevarchar(256))
as
begin
declare@sqlvarchar(8000)
declare@sqlValuesvarchar(8000)
set@sql="("
set@sqlValues="values(""+"
select@sqlValues=@sqlValues+cols+"+"",""+",@sql=@sql+"["+name+"],"
from
(selectcase
whenxtypein(48,52,56,59,60,62,104,106,108,122,127)
then"casewhen"+name+"isnullthen""NULL""else"+"cast("+name+"asvarchar)"+"end"
whenxtypein(58,61)
--then"""""""""+convert(char(23),"+name+",121)+"""""""""--datetime
then"casewhen"+name+"isnullthen""NULL""else"+"""""""""+"+"cast("+name+"asvarchar)"+"+"""""""""+"end"
whenxtypein(167)
then"casewhen"+name+"isnullthen""NULL""else"+"""""""""+"+"replace("+name+","""""""","""""""""""")"+"+"""""""""+"end"
whenxtypein(231)
then"casewhen"+name+"isnullthen""NULL""else"+"""N""""""+"+"replace("+name+","""""""","""""""""""")"+"+"""""""""+"end"
whenxtypein(175)
then"casewhen"+name+"isnullthen""NULL""else"+"""""""""+"+"cast(replace("+name+","""""""","""""""""""")asChar("+cast(lengthasvarchar)+"))+"""""""""+"end"
whenxtypein(239)
then"casewhen"+name+"isnullthen""NULL""else"+"""N""""""+"+"cast(replace("+name+","""""""","""""""""""")asChar("+cast(lengthasvarchar)+"))+"""""""""+"end"
else"""NULL"""
endasCols,name
fromsyscolumns
whereid=object_id(@tablename)
)T
set@sql="select""INSERTINTO["+@tablename+"]"+left(@sql,len(@sql)-1)+")"+left(@sqlValues,len(@sqlValues)-4)+")""from"+@tablename
print@sql
exec(@sql)
end
SQL语句
最后的结果:
INSERTINTO[SysSample]([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime])values("0002CA83-AF2F-4D8F-A345-33CA1CC7CF3C","任务调度系统",18,"2013-01-0221:42:30.013","",NULL,"2013-01-0221:42:30.013")
INSERTINTO[SysSample]([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime])values("0004A6F3-EC28-4D1F-BA40-0FC4B2218C92","任务调度系统",18,"2013-07-0919:36:00.060","",NULL,"2013-07-0919:36:00.060")
INSERTINTO[SysSample]([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime])values("00094D35-7B51-4EA3-871E-CE17E293B157","任务调度系统",18,"2013-05-1615:21:20.070","",NULL,"2013-05-1615:21:20.070")
INSERTINTO[SysSample]([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime])values("000BFBB0-B37D-4D6E-9FA2-3069D4F18F84","任务调度系统",18,"2013-04-1111:41:50.030","",NULL,"2013-04-1111:41:50.030")
INSERTINTO[SysSample]([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime])values("000C2CBC-E358-4469-BC2C-04F4DDCD72CD","任务调度系统",18,"2013-05-0616:07:00.037","",NULL,"2013-05-0616:07:00.037")
INSERTINTO[SysSample]([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime])values("000CB795-40EC-4783-B7A4-8D298DF63B70","任务调度系统",18,"2013-01-2320:52:30.030","",NULL,"2013-01-2320:52:30.030")
相关文章
- SQLSERVER backup 命令总结
- 的安装实现sqlserver实例的高效安装(sqlserver实例)
- 数据库优化SQL Server数据库的并行化技术(sqlserver并行)
- SQLServer 简化版:轻松管理你的数据库(sqlserver简化版)
- 小型SQL Server:实现数据存储的最佳选择(小型的sqlserver)
- SQLServer数据库中的分组技术(分组 sqlserver)
- 监控从SQLserver角度看待链路监控(sqlserver链路)
- SQLServer之表:存储数据最核心部分(sqlserver ?表)
- 使用SQLServer电子版轻松解决数据存储问题(sqlserver电子版)
- 据使用SQLServer实现正确数据存储(sqlserver 正数)
- 妙用SQLServer实现机试常胜(sqlserver机试)
- 六使用SQLServer迎接星期六的喜悦(sqlserver 星期)
- 接Sql Server 左联接技术:实现有效的数据查询(sqlserver 左联)
- 制SQLServer存储二进制数据的实现(sqlserver存二进)
- SQLServer 数据库中添加列的实现方法(sqlserver 加列)
- SQLServer加速查询的主从表技术(sqlserver主从表)
- 实现业务中梦想:购买SQLServer(购买sqlserver)