Create Procedure DBGENDATA? @Tablename? Varchar (100)? - Table name asdeclare @columnname varchar (100) ?? - Column name declare @Typename varchar (100) ?? - Data Type DECLARE @Columns VARCHAR (3000)? ?? - declare @columnsandhead, virchar (3000)? -
Set @ColumnsandHead = 'INSERT INTO' @ Tablename '(' set @columns = '"
- Get all the field names of the table DECLARE A? CURSOR for SELECT A. [Name] as columnname, b. [Name] as typeName from syscolumns a inner join system, in 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 Beginif @Typename in ('Bigint', 'bit', 'Decimal', 'Float', 'Int', 'Money', 'Numeric' , 'real', 'smallint', 'smallmoney', 'tinyint') beginset @columns = @Columns 'Isnull (Cast (' @ Columnname AS VARCHAR), '' ') ' ',' ' 'Endelsebeginset @columns = @ columns ?' '' '' '' ? Isnull (Cast (' @ Columnname ' As Varchar), '' ') ' '' '' '' '' '' ' ',' ' ' ENDSET @columnsAndhead = @columnsAndhead @columnName ',' ????? FETCH NEXT FROM a INTO @columnName, @ TypeName ENDSELECT @columnsAndhead = left (@ columnsAndhead, len (@columnsAndhead) -1 ) ') VALUES (' 'SELECT @Columns = Left (@ Column, Len (@Columns) -5) Close a? Deallocate a ?? EXEC (' SELECT '' @ ColumnAdhead ' ' @ Column ' ' ')' AS A from ' @ TableName) GO
