存储过程生成数据表的insert脚本(转)
2010-11-01 11:13 yuejianjun 阅读(278) 评论(0) 编辑 收藏 举报
USE [dajie]
GO
/****** 对象: StoredProcedure [dbo].[Spgeninsertsql] 脚本日期: 11/01/2010 11:06:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[Spgeninsertsql] (@Tablename Varchar(256))
As
Begin
/*当变量的字符数超过8000时,执行存储过程失败。*/
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 '+''''''''' + ' + '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 '+''''''''' + ' + 'cast(Replace('+ Name+','''''''','''''''''''') As Char(' + Cast(Length As Varchar) + '))+'''''''''+' End'
Else '''null'''
End As Cols,Name
From Syscolumns
Where (Id = Object_Id(@Tablename)) And (Autoval Is Null)
) T
Set @Sql ='select ''insert Into ['+ @Tablename + ']' + Left(@Sql,Len(@Sql)-1)+') ' + Left(@Sqlvalues,Len(@Sqlvalues)-4) + ')'' From '+@Tablename
--Print @Sql
Exec (@Sql)
End
http://www.cnblogs.com/bmib/archive/2010/10/27/1862673.html