将数据库表中的数据生成Insert脚本的存储过程!!!

答案：
CREATE Procedure DBGenData
?@tableName? Varchar(100)?--表名
AS
DECLARE @columnName VARCHAR(100)??--列名
DECLARE @TypeName VARCHAR(100)??--数据类型
DECLARE @columns VARCHAR(3000)???--
DECLARE @columnsAndhead VARCHAR(3000)?--

SET @columnsAndhead = 'INSERT INTO '+@tableName+'('
SET @columns = ''

?--获取表的所有字段名称
??? DECLARE a? CURSOR FOR 
?SELECT a.[NAME] AS ColumnName ,b.[NAME] AS TypeName 
?FROM syscolumns a INNER JOIN systypes b ON a.xtype=b.xtype 
?WHERE a.[id]=(SELECT [id] FROM sysobjects WHERE [NAME]=@tableName)

??? OPEN a
??? FETCH NEXT FROM a INTO @columnName ,@TypeName
??? WHILE @@FETCH_STATUS = 0
??? BEGIN
??IF @TypeName IN ('bigint','bit','decimal','float','int','money','numeric','real','smallint','smallmoney','tinyint')
??BEGIN
???SET @columns = @columns + 'ISNULL(CAST('+@columnName +' AS VARCHAR),'''')+'',''+'
??END
??ELSE
??BEGIN
???SET @columns = @columns+? '''''''''+? ISNULL(CAST('+@columnName +' AS VARCHAR),'''')+ ''''''''+'',''+'
??END
??SET @columnsAndhead = @columnsAndhead + @columnName +','????? 
??????? FETCH NEXT FROM a INTO @columnName ,@TypeName
??? END
?SELECT @columnsAndhead = left(@columnsAndhead,len(@columnsAndhead)-1) +') VALUES('''
?SELECT @columns = left(@columns,len(@columns)-5)
??? CLOSE a?
??? DEALLOCATE a?? 
?
?exec('select '''+@columnsAndhead+'+'+@columns +'+'')'' as a from '+@tableName)

GO

上一个：中英文字符混合处理方法
下一个：怎样解决SQL Server：“Could not create component categories manager”和“Could not create an insta..