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
|
本类阅读TOP10