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

/****** Object:  StoredProcedure [dbo].[Spgeninsertsql]    Script Date: 10/27/2010 15:30:34 ******/


CREATE 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
GO