生成Insert语句SQL存储过程

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

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(1000)),''null'')+'',''+'
   END
  ELSE
   BEGIN
    SET @columns = @columns+  ' (case when '+@columnName +' is null then '''' else '''''''' end) + isnull(CAST('+@columnName +' AS VARCHAR(1000)),''null'')+ (case when '+@columnName +' is null then '''' else '''''''' end) +'',''+'
   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)
print 'select '''+@columnsAndhead+'+'+@columns +'+'')'' as a from '

GO