存储过程生成数据表的insert脚本（转）

 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