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"）