SQL Server Performance

SP to fill a table

Discussion in 'Contribute Your SQL Server Scripts' started by ramkumar.mu, May 3, 2006.

  1. ramkumar.mu New Member

    IF object_id('SP_FillTable') IS NOT NULL
    BEGIN
    PRINT 'Procedure SP_FillTable dropped'
    DROP PROCEDURE SP_FillTable
    END
    Go
    /*****************************************************************************************************************
    Procedure Name : SP_FillTable
    Author Name : Ramkumar Murugesan
    Creation Date : 13 April 2006

    Parameters
    ----------

    1) Name of the Table (compulsary)
    2) Number of rows to be inserted (if nothing is specified, 100 rows will be inserted)

    Restrictions
    ------------

    1) Cannot Insert the below mentioned datatypes
    a) sql_variant, timestamp, uniqueidentifier, image
    2) SP will fail if any of column has a foreign key association
    3) Cannot insert system tables

    Algorithm
    ---------
    1) Gets the Name of the column, its Datatype and length from Information_schema.columns table
    and a new column 'GroupDataType' is added to reduce the complexity of assigning values.
    2) Column names are appended in a variable separated by commas (To append to an insert statement)
    3) Values are determined on the basis of a random value and assigned to one of the three variables,
    intValue if the column is of integer type, vcrValue if the column is float type and so on...
    4) Values of int, float, varchar are consolidated into a single variable
    5) Values are filled into the table
    6) Steps 3-5 are repeated for the count that is passed as a parameter (@RowCount) and
    the table is filled with '@Rowcount' records.

    Execution
    ---------
    1) SP_FillTable tbl_Individual, 1000
    2) SP_FillTable tbl_Organization

    ****************************************************************************************************************/

    CREATE PROCEDURE SP_FillTable
    (
    @TableName varchar(256)
    ,@RowCount int = NULL
    )
    AS

    SET NOCOUNT ON

    -- Declaring procedure variables
    DECLARE @Columns varchar(1000)
    ,@Values varchar(7000)
    ,@intValues varchar(1500)
    ,@fltValues varchar(1500)
    ,@vcrValues varchar(4000)
    ,@Query varchar(8000)
    ,@BigText varchar(8000)
    ,@GroupDataType varchar(100)
    ,@DataType varchar(100)
    ,@RandomNumber int
    ,@Rows smallint
    ,@i smallint
    ,@j smallint
    ,@Length smallint
    -- Declaring error handling values
    ,@ErrMsg varchar(250)
    ,@Action varchar(20)
    ,@Object varchar(20)
    ,@Type varchar(20)
    ,@Error smallint

    -- Initializing values
    SELECT @RowCount = coalesce(@RowCount, 100)
    ,@Columns = ''
    ,@BigText = 'Microsoft SQL Server 2000 includes several components you can use to build data warehouses that effectively support your decision support processing needs. Data Warehousing Framework The Data Warehousing Framework is a set of components and APIs that implement the data warehousing features of SQL Server 2000. It provides a common interface to be used by various components seeking to build and use a data warehouse or data mart. Data Transformation Services Data Transformation Services (DTS) provides a set of services used to build a data warehouse or data mart. Decision support systems analyze data to find trends of interest to the database users. Online transaction processing databases store large numbers of records covering the details of each transaction, and online analytical processing (OLAP) systems aggregate and summarize the information to speed analysis of the trends exhibited in the data. DTS offers support for extracting data from heterogeneous OLE DB data sources and the summarizing or aggregating of data to build a data warehouse. Online Analytical Processing Support Microsoft SQL Server 2000 Analysis Services allows you to build flexible, powerful business intelligence applications for Web sites and large enterprise systems. Microsoft SQL Server 2000 Analysis Services provides OLAP processing capabilities against heterogeneous OLE DB data sources. It has efficient algorithms for defining and building multidimensional cubes that can be referenced by applications using the OLE DB 2.0 OLAP extensions or the Microsoft ActiveX® Data Objects Multidimensional extensions. Analysis Services is an excellent tool for multidimensional analysis of data in SQL Server 2000 databases. Analysis Services supports multidimensional queries against cubes with hundreds of millions of dimensions. You can control cube security down to the level of cells and members. You can create custom rollup functions that tailor the types of aggregations and processing that can be performed in multidimensional cubes.
    Data Mining Support Data mining allows you to define models containing grouping and predictive rules that can be applied to data in either a relational database or multi-dimensional OLAP cubes. These predictive models are then used to automatically perform sophisticated analysis of the data to find trends that help you identify new opportunities and chose the ones that have a winning outcome. SQL Server 2000 Analysis Services includes support for data mining models, including API support of the OLE DB for Data Mining specification. Through the OLE DB for Data Mining API, Analysis Services supports integration with third-party data mining providers. English Query English Query makes a definition of the entities and relationships defined in a SQL Server 2000 database. Given this definition, an application can use an Automation API to pass English Query a string containing a natural-language question about the data in the database. English Query returns a SQL statement that the application can use to extract the necessary data. Meta Data Services SQL Server 2000 includes Microsoft Meta Data Services, which consists of a set of Microsoft ActiveX® interfaces and information models that define database schema and data transformations as defined by the Microsoft Data Warehousing Framework. A goal of the Microsoft Data Warehousing Framework is to provide meaningful integration of multiple products through shared meta data. It combines business and technical meta data to provide an industry standard method for storing the schema of production data sources and destinations.
    Meta Data Services is the preferred means of storing DTS packages in a data warehousing scenario because it is the only method of providing data lineage for packages. DTS also uses Meta Data Services storage to allow transformations, queries, and ActiveX scripts to be reused by heterogeneous applications.'

    -- 1) Get all the columns and their datatype for the given table
    IF object_id('tempdb..##columns') IS NOT NULL DROP TABLE ##Columns
    SELECT Identity(int,1,1) AS IdentityColumn
    ,Column_Name AS ColumnName
    ,Data_Type AS DataType
    ,Data_Type AS GroupDataType
    ,Character_Maximum_length AS Length
    INTO ##Columns
    FROM Information_Schema.Columns
    WHERE 1 = 2

    SELECT @Rows = @@rowcount, @Action = 'Creating', @Object = '##Columns', @Type = 'Table', @Error = @@error
    IF @Error <> 0 GOTO Crash

    INSERT INTO ##columns
    SELECT Column_Name AS ColumnName
    ,Data_Type AS DataType
    ,GroupDataType = CASE Data_Type WHEN 'float' THEN 'real'
    WHEN 'real' THEN 'real'
    WHEN 'numeric' THEN 'real'
    WHEN 'decimal' THEN 'real'
    WHEN 'smallmoney' THEN 'real'
    WHEN 'money' THEN 'real'
    WHEN 'bigint' THEN 'int'
    WHEN 'int' THEN 'int'
    WHEN 'Smallint' THEN 'int'
    WHEN 'tinyint' THEN 'int'
    WHEN 'bit' THEN 'int'
    WHEN 'char' THEN 'varchar'
    WHEN 'nchar' THEN 'varchar'
    WHEN 'ntext' THEN 'varchar'
    WHEN 'nvarchar' THEN 'varchar'
    WHEN 'Varchar' THEN 'varchar'
    WHEN 'text' THEN 'varchar'
    WHEN 'datetime' THEN 'varchar'
    WHEN 'smalldatetime' THEN 'varchar'
    ELSE 'varchar'
    END
    ,Character_Maximum_Length As Length
    FROM Information_Schema.Columns
    WHERE Table_Name = @TableName
    AND Data_Type NOT IN ('sql_variant','timestamp','uniqueidentifier','image')
    AND NOT EXISTS (SELECT 1 FROM syscolumns
    WHERE id = object_id(@TableName)
    AND Name = Column_Name
    AND Colstat IN (1,4))
    ORDER BY GroupDataType
    ,DataType
    SELECT @Rows = @@rowcount, @Action = 'Inserting', @Object = '##Columns', @Type = 'Table'
    IF @Rows = 0
    BEGIN
    RAISERROR('Specified a Invalid Table or System Table',18,126)
    return
    END
    IF @error <> 0 GOTO Crash

    -- 2) Assign the column names into a single variable
    SELECT @i = 1
    WHILE @i <= @Rows
    BEGIN
    SELECT @Columns = @columns + ColumnName + ',' FROM ##Columns WHERE IdentityColumn = @i
    SELECT @i = @i + 1
    END
    SELECT @Columns = left(@Columns,len(@Columns)-1)

    -- Total number of values to be inserted
    WHILE @RowCount > 0
    BEGIN
    -- Assigning Values that should hold its value till the while loop
    SELECT @RandomNumber = convert(int,right((rand(left(@@idle,5)*1000)),4))
    ,@Values = ''
    ,@intValues = ''
    ,@fltValues = ''
    ,@vcrValues = ''
    ,@Query = ''
    SELECT @j = 1

    -- 3) Assign Random Values to the Columns
    WHILE @j <= @Rows
    BEGIN
    SELECT @GroupDataType = GroupDataType
    ,@Datatype = DataType
    ,@Length = Length
    FROM ##Columns
    WHERE IdentityColumn = @j
    SELECT @Action = 'Selecting', @Object = '##Columns', @Type = 'Table',@error = @@error
    IF @error <> 0 GOTO Crash

    IF @GroupDataType = 'int'
    BEGIN
    SELECT @intValues = @intValues
    + convert(varchar,CASE @DataType WHEN 'bigint' THEN convert(int,datepart(millisecond, getdate())*((@randomnumber+@@total_read)/1000))
    WHEN 'int' THEN convert(int,datepart(millisecond, getdate())*((@randomnumber+@@total_read)/1000))
    WHEN 'Smallint' THEN convert(int,datepart(millisecond, getdate())*((@randomnumber+@@total_read)/1000))
    WHEN 'tinyint' THEN convert(int,datepart(millisecond, getdate())*((@randomnumber+@@total_read)/1000))
    WHEN 'bit' THEN (CASE WHEN right(convert(int,rand(@RandomNumber)),1) > 5 THEN 1 ELSE 0 END)
    ELSE 0
    END)
    + '9911'
    SELECT @RandomNumber = @RandomNumber + convert(int,rand(@j*datepart(millisecond, getdate()))*1000)
    END -- End of int IF
    ELSE IF @GroupDataType = 'real'
    BEGIN
    SELECT @fltValues = @fltValues
    + CASE @DataType WHEN 'decimal' THEN convert(real,(rand(@RandomNumber) * 1000))
    WHEN 'float' THEN convert(real,(rand(@RandomNumber) * 1000))
    WHEN 'numeric' THEN convert(real,(rand(@RandomNumber) * 1000))
    WHEN 'real' THEN convert(real,(rand(@RandomNumber) * 1000))
    WHEN 'money' THEN convert(real,(rand(@RandomNumber) * 1000))
    WHEN 'smallmoney' THEN convert(real,(rand(@RandomNumber) * 1000))
    ELSE 0.0
    END
    + '9911'
    SELECT @RandomNumber = @RandomNumber + convert(int,rand(datepart(millisecond, getdate())*@j)*10000)
    END -- End of real IF
    ELSE IF @GroupDataType = 'varchar'
    BEGIN
    SELECT @vcrvalues = @vcrValues
    + ''''
    + CASE @DataType WHEN 'char' THEN left(substring(@BigText,convert(int,replace(right(convert(varchar(30),rand(@RandomNumber)*100),2),'.','')),convert(int,replace(right(convert(varchar(30),rand(@j)),2),'.',''))),@length)
    WHEN 'nchar' THEN left(substring(@BigText,convert(int,replace(right(convert(varchar(30),rand(@RandomNumber+3)*100),2),'.','')),convert(int,replace(right(convert(varchar(30),rand(@j)),2),'.',''))),@length)
    WHEN 'ntext' THEN left(substring(@BigText,convert(int,replace(right(convert(varchar(30),rand(@RandomNumber+6)*100),3),'.','')),convert(int,replace(right(convert(varchar(30),rand(@j)),2),'.',''))),@length)
    WHEN 'nvarchar' THEN left(substring(@BigText,convert(int,replace(right(convert(varchar(30),rand(@RandomNumber+2)*100),3),'.','')),convert(int,replace(right(convert(varchar(30),rand(@j)),2),'.',''))),@length)
    WHEN 'varchar' THEN left(substring(@BigText,convert(int,replace(right(convert(varchar(30),rand(@RandomNumber+2)*100),3),'.','')),convert(int,replace(right(convert(varchar(30),rand(@j)),2),'.',''))),@length)
    WHEN 'text' THEN left(substring(@BigText,convert(int,replace(right(convert(varchar(30),rand(@RandomNumber+5)*100),3),'.','')),convert(int,replace(right(convert(varchar(30),rand(@j)),2),'.',''))),@length)
    WHEN 'datetime' THEN convert(varchar(25),'1900-00-00 12:00:00')
    WHEN 'smalldatetime' THEN convert(varchar(25),'1900-00-00 12:00:00')
    ELSE left(substring(@BigText,convert(int,replace(right(convert(varchar(30),rand(@RandomNumber)),4),'.','')),convert(int,replace(right(convert(varchar(30),rand(@j)),2),'.',''))),@length)
    END
    + ''''
    + ','
    SELECT @RandomNumber = @RandomNumber + convert(int,rand(datepart(millisecond, getdate())*@j)*100)
    END -- End of varchar IF

    SELECT @j = @j + 1
    END

    -- 4) Consolidating all the values into a single variable
    SELECT @intValues = replace(@intValues,'9911',',')
    ,@fltValues = replace(@fltValues,'9911',',')
    ,@Values = left((@intValues + @fltValues + @vcrValues), len(@intValues + @fltValues + @vcrValues)-1)
    ,@Query = 'INSERT INTO ' + @TableName + '(' + @Columns + ') values(' + @Values + ')'

    -- 5) Filling values into the target table
    SET NOCOUNT OFF
    EXEC (@query)
    SELECT @Action = 'Inserting', @Object = @TableName, @Type = 'Table',@error = @@error
    IF @error <> 0 GOTO Crash
    SET NOCOUNT ON

    SELECT @RowCount = @RowCount - 1
    END -- Rowcount end
    RETURN

    Crash:

    SELECT @ErrMsg = 'Error ' + @Action + ' ' + @Type + ' ' + @Object
    RAISERROR(@ErrMsg,18,126)
    RETURN -200

    Go
    IF @@error = 0
    PRINT 'Procedure SP_FillTable created'
    Go



    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  2. FrankKalis Moderator

    Personally I use this one:<a target="_blank" href=http://research.microsoft.com/%7EGray/DBGen/>http://research.microsoft.com/%7EGray/DBGen/</a> [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  3. ramkumar.mu New Member

    If i had known about this link some 15 days back, i wouldnt have created this SP [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]. still, i have lot of drawbacks like - failing at foreign key violations. i thought of filling the rows in the master tables too, but this went too complex and so i stopped here.<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
  4. ramkumar.mu New Member

    Fixed that foreign key failure bug... Modified code...<br /><br />IF object_id('SP_FillTable') IS NOT NULL <br />BEGIN<br /> PRINT 'Procedure SP_FillTable dropped'<br /> DROP PROCEDURE SP_FillTable<br />END<br />Go<br />/*****************************************************************************************************************<br />Procedure Name : SP_FillTable<br />Author Name : Ramkumar Murugesan<br />Creation Date : 12 April 2006<br /><br />Parameters <br />----------<br /><br />1) Name of the Table (compulsary)<br />2) Number of rows to be inserted (if nothing is specified, 100 rows will be inserted)<br />3) Order of the integer values (Random or Increment values are accepted)<br /><br />Restrictions <br />------------<br /><br />1) Cannot Insert the below mentioned datatypes<br /> a) sql_variant, timestamp, uniqueidentifier, image<br />2) Cannot insert system tables<br /><br />Algorithm<br />---------<br />1) Gets the Name of the column, its Datatype and length from Information_schema.columns table <br /> and a new column 'GroupDataType' is added to reduce the complexity of assigning values.<br />2) Column names are appended in a variable separated by commas (To append to an insert statement)<br />3) Values are determined on the basis of a random value and assigned to one of the three variables, <br /> intValue if the column is of integer type, vcrValue if the column is float type and so on...<br />4) Values of int, float, varchar are consolidated into a single variable<br />5) Values are filled into the table<br />6) Steps @Length-5 are repeated for the count that is passed as a parameter (@RowCount) and <br /> the table is filled with '@Rowcount' records.<br />7) If Increment is selected as FillType then the integer values will be filled in incrementing order.<br /><img src='/community/emoticons/emotion-11.gif' alt='8)' /> For filling data as 1,2,3... change the column to identity increment.<br /><br />Execution<br />---------<br />1) SP_FillTable tbl_Individual, 1000, 'Increment'<br />2) SP_FillTable tbl_Organization, 1000<br />3) SP_FillTable tbl_Individual, NULL, 'Increment'<br />4) SP_FillTable tbl_Organization<br /><br />Change History<br />--------------<br /><br />Date Modification<br />---- ------------<br />10-Nov-2006 Previously the SP would fail if table has columns with foreign key constraints. Now added the feature<br /> so that tables with foreign columns can also be filled using this SP. <br />****************************************************************************************************************/<br /><br />CREATE PROCEDURE SP_FillTable<br />(<br /> @TableName varchar(256)<br /> ,@RowCount int = 100<br /> ,@FillType varchar(20) = 'Random'<br />)<br />AS<br /><br />SET NOCOUNT OFF<br /><br />-- Declaring procedure variables<br />DECLARE @Columns varchar(1000)<br /> ,@Values varchar(7000)<br /> ,@intValues varchar(1500)<br /> ,@fltValues varchar(1500) <br /> ,@vcrValues varchar(4000) <br /> ,@dtValues varchar(500)<br /> ,@Query varchar(8000) <br /> ,@BigText varchar(8000)<br /> ,@GroupDataType varchar(100)<br /> ,@DataType varchar(100)<br /> ,@DyanSQL nvarchar(4000)<br /> ,@DyanParam nvarchar(4000)<br /> ,@i int<br /> ,@j int <br /> ,@si int<br /> ,@ti int <br /> ,@RandomNumber int <br /> ,@Rows int <br /> ,@Length int <br /> ,@min int <br /> ,@RandNum int <br /> ,@Variable1 int <br />-- Variables for Foreign Key<br /> ,@fkeyColName varchar(50)<br /> ,@fkeyTabName varchar(50)<br /> ,@IsForeignKey bit<br /> ,@fkeyRowCount int<br /> ,@fkeyRowNo int<br /> ,@fkeyvalues varchar(4000)<br /> ,@fkeyvalue varchar(4000) <br />-- Declaring error handling values<br /> ,@ErrMsg varchar(250)<br /> ,@Action varchar(20)<br /> ,@Object varchar(20)<br /> ,@Type varchar(20)<br /> ,@Error int<br /><br />-- Initializing values<br />SELECT @RowCount = coalesce(@RowCount, 100)<br /> ,@Columns = ''<br /> ,@BigText= 'Microsoft SQL Server 2000 includes several components you can use to build data warehouses that effectively support your decision support processing needs. Data Warehousing Framework The Data Warehousing Framework is a set of components and APIs that implement the data warehousing features of SQL Server 2000. It provides a common interface to be used by various components seeking to build and use a data warehouse or data mart. Data Transformation Services Data Transformation Services (DTS) provides a set of services used to build a data warehouse or data mart. Decision support systems analyze data to find trends of interest to the database users. Online transaction processing databases store large numbers of records covering the details of each transaction, and online analytical processing (OLAP) systems aggregate and summarize the information to speed analysis of the trends exhibited in the data. DTS offers support for extracting data from heterogeneous OLE DB data sources and the summarizing or aggregating of data to build a data warehouse. Online Analytical Processing Support Microsoft SQL Server 2000 Analysis Services allows you to build flexible, powerful business intelligence applications for Web sites and large enterprise systems. Microsoft SQL Server 2000 Analysis Services provides OLAP processing capabilities against heterogeneous OLE DB data sources. It has efficient algorithms for defining and building multidimensional cubes that can be referenced by applications using the OLE DB 2.0 OLAP extensions or the Microsoft ActiveX® Data Objects Multidimensional extensions. Analysis Services is an excellent tool for multidimensional analysis of data in SQL Server 2000 databases. Analysis Services supports multidimensional queries against cubes with hundreds of millions of dimensions. You can control cube security down to the level of cells and members. You can create custom rollup functions that tailor the types of aggregations and processing that can be performed in multidimensional cubes. <br /> Data Mining Support Data mining allows you to define models containing grouping and predictive rules that can be applied to data in either a relational database or multi-dimensional OLAP cubes. These predictive models are then used to automatically perform sophisticated analysis of the data to find trends that help you identify new opportunities and chose the ones that have a winning outcome. SQL Server 2000 Analysis Services includes support for data mining models, including API support of the OLE DB for Data Mining specification. Through the OLE DB for Data Mining API, Analysis Services supports integration with third-party data mining providers. English Query English Query makes a definition of the entities and relationships defined in a SQL Server 2000 database. Given this definition, an application can use an Automation API to pass English Query a string containing a natural-language question about the data in the database. English Query returns a SQL statement that the application can use to extract the necessary data. Meta Data Services SQL Server 2000 includes Microsoft Meta Data Services, which consists of a set of Microsoft ActiveX® interfaces and information models that define database schema and data transformations as defined by the Microsoft Data Warehousing Framework. A goal of the Microsoft Data Warehousing Framework is to provide meaningful integration of multiple products through shared meta data. It combines business and technical meta data to provide an industry standard method for storing the schema of production data sources and destinations.<br /> Meta Data Services is the preferred means of storing DTS packages in a data warehousing scenario because it is the only method of providing data lineage for packages. DTS also uses Meta Data Services storage to allow transformations, queries, and ActiveX scripts to be reused by heterogeneous applications.'<br /> ,@RandNum = round(0 + (rand() * (100)),0)<br /><br />-- 1) Get all the columns and their datatype for the given table<br />IF object_id('tempdb..#Columns') IS NOT NULL DROP TABLE #Columns<br />SELECT Identity(int,1,1) AS IdentityColumn<br /> ,Column_Name AS ColumnName <br /> ,Data_Type AS DataType <br /> ,Data_Type AS GroupDataType <br /> ,Character_Maximum_length AS Length <br /> ,IsForeignKey = 1<br /> INTO #Columns <br /> FROM Information_Schema.Columns <br /> WHERE 1 = 2 <br /><br />SELECT @Rows = @@rowcount, @Action = 'Creating', @Object = '#Columns', @Type = 'Table', @Error = @@error<br />IF @Error &lt;&gt; 0 GOTO Crash <br /><br />INSERT INTO #Columns<br />SELECT Column_Name AS ColumnName <br /> ,Data_Type AS DataType <br /> ,GroupDataType = CASE Data_Type WHEN 'float' THEN 'real'<br /> WHEN 'real' THEN 'real' <br /> WHEN 'numeric' THEN 'real' <br /> WHEN 'decimal' THEN 'real' <br /> WHEN 'smallmoney' THEN 'real'<br /> WHEN 'money' THEN 'real'<br /> WHEN 'bigint' THEN 'int'<br /> WHEN 'int' THEN 'int'<br /> WHEN 'Smallint' THEN 'int'<br /> WHEN 'tinyint' THEN 'int'<br /> WHEN 'bit' THEN 'int' <br /> WHEN 'char' THEN 'varchar'<br /> WHEN 'nchar' THEN 'varchar'<br /> WHEN 'ntext' THEN 'varchar'<br /> WHEN 'nvarchar' THEN 'varchar'<br /> WHEN 'Varchar' THEN 'varchar'<br /> WHEN 'text' THEN 'varchar' <br /> WHEN 'datetime' THEN 'datetime' <br /> WHEN 'smalldatetime' THEN 'datetime' <br /> ELSE 'varchar' <br /> END<br /> ,Character_Maximum_Length As Length<br /> ,IsForeignKey = CASE WHEN Column_Name IN ((SELECT name <br /> FROM SYSCONSTRAINTS B <br /> INNER JOIN SYSCOLUMNS A <br /> ON object_name(A.id) = object_name(B.id)<br /> AND B.colid = A.colid<br /> AND object_name(A.id) = @TableName))<br /> THEN 1<br /> ELSE 0<br /> END <br /> FROM Information_Schema.Columns <br /> WHERE Table_Name = @TableName <br /> AND Data_Type NOT IN ('sql_variant','timestamp','uniqueidentifier','image','binary','varbinary')<br /> AND NOT EXISTS (SELECT 1 FROM syscolumns <br /> WHERE id = object_id(@TableName)<br /> AND Name = Column_Name<br /> AND Colstat IN (1,4)) <br /> ORDER BY IsForeignKey<br /> ,GroupDataType<br /> ,DataType<br /> <br />SELECT @Rows = @@rowcount, @Action = 'Inserting', @Object = '#Columns', @Type = 'Table'<br />IF @Rows = 0 <br />BEGIN<br />RAISERROR('Insert Failed. Specified a TableName that is either a system table or doesn''t exist',18,126)<br />return<br />END<br />IF @error &lt;&gt; 0 GOTO Crash <br /><br />-- 2) Assign the column names into a single variable<br />SELECT @i = 1<br />WHILE @i &lt;= @Rows <br />BEGIN<br /> SELECT @Columns = @columns + ColumnName + ',' FROM #Columns WHERE IdentityColumn = @i<br /> SELECT @i = @i + 1<br />END<br />SELECT @Columns = left(@Columns,len(@Columns)-1)<br /><br />SELECT @i = 1 -- for filling increment values<br /> ,@si = 1<br /> ,@ti = 1<br /><br />-- Total number of values to be inserted<br />WHILE @RowCount &gt; 0<br />BEGIN<br /> SELECT @si = CASE WHEN @si = 32766 THEN 1 ELSE @si END<br /> ,@ti = CASE WHEN @ti = 127 THEN 1 ELSE @ti END<br /> -- 1) Assigning Values that should hold its value till the while loop<br /> SELECT @RandomNumber = round(0 + (rand() * (50-0)),0)<br /> ,@Values = ''<br /> ,@intValues = ''<br /> ,@fltValues = ''<br /> ,@vcrValues = ''<br /> ,@dtValues = ''<br /> ,@fkeyvalues = ''<br /> ,@Query = ''<br /> ,@j = 1<br /><br /> -- 2) Assign Random Values to the Columns<br /> WHILE @j &lt;= @Rows <br /> BEGIN<br /> SELECT @Variable1 = 135<br /> SELECT @GroupDataType = GroupDataType <br /> ,@Datatype = DataType <br /> ,@Length = Length <br /> ,@IsForeignKey = IsForeignKey<br /> FROM #Columns <br /> WHERE IdentityColumn = @j<br /> SELECT @Action = 'Selecting', @Object = '#Columns', @Type = 'Table',@error = @@error<br /> IF @error &lt;&gt; 0 GOTO Crash <br /><br /> IF @IsForeignKey = 0<br /> BEGIN<br /> IF @GroupDataType = 'int'<br /> BEGIN<br /> IF @FillType = 'Increment'<br /> BEGIN<br /> SELECT @intValues = @intValues <br /> + convert(varchar,CASE @DataType WHEN 'bigint' THEN ((@RandNum + @i) + (@j*@Variable1))<br /> WHEN 'int' THEN ((@RandNum + @i) + (@j*@Variable1))<br /> WHEN 'Smallint' THEN @si -- added to prevent SP failing from arith-overflow-errors<br /> WHEN 'tinyint' THEN @ti -- added to prevent SP failing from arith-overflow-errors<br /> WHEN 'bit' THEN (CASE WHEN right(convert(int,rand(@RandNum)),1) &gt; 5 THEN 1 ELSE 0 END)<br /> ELSE 0<br /> END)<br /> + '9848265374' <br /> END<br /> ELSE<br /> BEGIN<br /> SELECT @intValues = @intValues <br /> + convert(varchar,CASE @DataType WHEN 'bigint' THEN @RandomNumber * round(0 + (rand() * (100-0)),0)<br /> WHEN 'int' THEN @RandomNumber * round(0 + (rand() * (50-0)),0)<br /> WHEN 'Smallint' THEN @RandomNumber * round(0 + (rand() * (20-0)),0)<br /> WHEN 'tinyint' THEN @RandomNumber * round(0 + (rand()),0)<br /> WHEN 'bit' THEN (CASE WHEN right(convert(int,rand(@RandomNumber)),1) &gt; 5 THEN 1 ELSE 0 END)<br /> ELSE 0<br /> END)<br /> + '9848265374' <br /> END <br /> END -- End of int IF<br /> ELSE IF @GroupDataType = 'real'<br /> BEGIN <br /> SELECT @fltValues = @fltValues <br /> + convert(varchar,CASE @DataType WHEN 'decimal' THEN convert(real,(rand() * 1000))<br /> WHEN 'float' THEN convert(real,(rand() * 1000))<br /> WHEN 'numeric' THEN convert(real,(rand() * 1000))<br /> WHEN 'real' THEN convert(real,(rand() * 1000))<br /> WHEN 'money' THEN convert(real,(rand() * 1000))<br /> WHEN 'smallmoney' THEN convert(real,(rand() * 1000))<br /> ELSE 0.0<br /> END)<br /> + '9848265374' <br /> END -- End of real IF <br /> ELSE IF @GroupDataType = 'varchar'<br /> BEGIN <br /> SELECT @vcrvalues = @vcrValues <br /> + '''' <br /> + CASE @DataType WHEN 'char' THEN left(substring(@BigText,convert(int,rand()*25),convert(int,rand()*35)),@length) <br /> WHEN 'nchar' THEN left(substring(@BigText,convert(int,rand()*550),convert(int,rand()*35)),@length) <br /> WHEN 'ntext' THEN left(substring(@BigText,convert(int,rand()*125),convert(int,rand()*100)),@length) <br /> WHEN 'nvarchar' THEN left(substring(@BigText,convert(int,rand()*200),convert(int,rand()*35)),@length) <br /> WHEN 'varchar' THEN left(substring(@BigText,convert(int,rand()*300),convert(int,rand()*35)),@length) <br /> WHEN 'text' THEN left(substring(@BigText,convert(int,rand()*250),convert(int,rand()*1000)),@length) <br /> ELSE left(substring(@BigText,convert(int,rand()*250),convert(int,rand()*100)),@length) <br /> END <br /> + '''' <br /> + ',' <br /> END -- End of varchar IF<br /> ELSE IF @GroupDataType = 'datetime'<br /> BEGIN <br /> SELECT @dtvalues = @dtValues <br /> + '''' <br /> + CASE @DataType WHEN 'datetime' THEN convert(varchar,getdate()- @RandomNumber)<br /> ELSE convert(varchar,convert(smalldatetime,getdate()- @RandomNumber))<br /> END <br /> + '''' <br /> + ',' <br /> END -- End of datetime IF<br /> END<br /> ELSE <br /> BEGIN<br /> SELECT @fkeyColName = name<br /> ,@fkeyTabName = object_name(a.id) <br /> FROM syscolumns A <br /> INNER JOIN sysforeignkeys B<br /> ON object_name(a.id) = object_name(b.rkeyid)<br /> AND b.rkey = a.colid<br /> WHERE object_name(b.fkeyid) = @TableName<br /><br /> SELECT @DyanSQL = 'SELECT @fkeyRowCount1 = count(*) FROM '+ @fkeyTabName<br /> SELECT @DyanParam = '@fkeyRowCount1 int OUTPUT'<br /> EXEC sp_executesql @DyanSQL, @DyanParam, @fkeyRowCount1 = @fkeyRowCount OUTPUT<br /><br /> SELECT @fkeyRowNo = round(0 + (rand() * ((@fkeyRowCount-1)-0)),0) <br /><br /> SELECT @DyanSQL = 'SELECT TOP 1 @fkeyvalue1 = ' + @fkeyColName + ' <br /> FROM ( SELECT TOP ' + convert(varchar(10),isnull(@fkeyRowNo,0)) + ' ' + @fkeyColName <br /> + ' FROM ' + @fkeyTabName + ' ORDER BY ' + @fkeyColName + ') SubQuery <br /> ORDER BY ' + @fkeyColName + ' DESC'<br /> SELECT @DyanParam = '@fkeyvalue1 varchar(200) OUTPUT'<br /> EXEC sp_executesql @DyanSQL, @DyanParam, @fkeyvalue1 = @fkeyvalue OUTPUT<br /><br /> SELECT @fkeyvalues = @fkeyvalues + @fkeyvalue + '9848265374' <br /> END<br /> SELECT @j = @j + 1<br /> END<br /><br /> -- 4) Consolidating all the values into a single variable <br /> SELECT @intValues = replace(@intValues,'9848265374',',')<br /> ,@fltValues = replace(@fltValues,'9848265374',',')<br /> ,@fkeyvalues = replace(@fkeyvalues,'9848265374',',')<br /> ,@Values = left(@dtValues + @intValues + @fltValues + @vcrValues + @fkeyvalues , len(@intValues + @fltValues + @vcrValues + @dtValues + @fkeyvalues)-1)<br /> ,@Query = 'INSERT INTO ' + @TableName + '(' + @Columns + ') values(' + @Values + ')' <br /><br /> -- 5) Filling values into the target table<br /> SET NOCOUNT OFF<br /> EXEC (@query) <br /> SELECT @Action = 'Inserting', @Object = @TableName, @Type = 'Table',@error = @@error<br /> IF @error &lt;&gt; 0 GOTO Crash <br /> SET NOCOUNT ON<br /> <br /> SELECT @i = @i + 1<br /> ,@si = @si + 1<br /> ,@ti = @ti + 1<br /> SELECT @RowCount = @RowCount - 1<br />END -- Rowcount end<br />RETURN<br /><br />Crash:<br /><br />SELECT @ErrMsg = 'Error ' + @Action + ' ' + @Type + ' ' + @Object<br />RAISERROR(@ErrMsg,18,126)<br />RETURN -200<br /><br />Go<br />IF @@error = 0 <br />PRINT 'Procedure SP_FillTable created'<br />Go<br /><br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
  5. ramkumar.mu New Member

    New Improved version which takes care of foreign keys...<br /><br />IF object_id('USP_FillTable') IS NOT NULL <br />BEGIN<br /> PRINT 'Procedure USP_FillTable dropped'<br /> DROP PROCEDURE USP_FillTable<br />END<br />Go<br />/*****************************************************************************************************************<br />Procedure Name : USP_FillTable<br />Author Name : Ramkumar Murugesan<br />Creation Date : 12 April 2006<br /><br />Parameters <br />----------<br /><br />1) Name of the Table (compulsary)<br />2) Number of rows to be inserted (if nothing is specified, 100 rows will be inserted)<br />3) Order of the integer values (Random or Increment values are accepted)<br /><br />Restrictions <br />------------<br /><br />1) Cannot Insert the below mentioned datatypes<br /> a) sql_variant, timestamp, uniqueidentifier, image<br />2) Cannot insert system tables<br /><br />Algorithm<br />---------<br />1) Gets the Name of the column, its Datatype and length from Information_schema.columns table <br /> and a new column 'GroupDataType' is added to reduce the complexity of assigning values.<br />2) Column names are appended in a variable separated by commas (To append to an insert statement)<br />3) Values are determined on the basis of a random value and assigned to one of the three variables, <br /> intValue if the column is of integer type, vcrValue if the column is float type and so on...<br />4) Values of int, float, varchar are consolidated into a single variable<br />5) Values are filled into the table<br />6) Steps @Length-5 are repeated for the count that is passed as a parameter (@RowCount) and <br /> the table is filled with '@Rowcount' records.<br />7) If Increment is selected as FillType then the integer values will be filled in incrementing order.<br /><img src='/community/emoticons/emotion-11.gif' alt='8)' /> For filling data as 1,2,3... change the column to identity increment.<br /><br />Execution<br />---------<br />1) USP_FillTable tbl_Individual, 1000, 'Increment'<br />2) USP_FillTable tbl_Organization, 1000<br />3) USP_FillTable tbl_Individual, NULL, 'Increment'<br />4) USP_FillTable tbl_Organization<br /><br />Change History<br />-------------------------------------------------------------------------------------------------------------------<br />Date Modification<br />-------------------------------------------------------------------------------------------------------------------<br />10-Nov-2006 Previously the SP would fail if table has columns with foreign key constraints. Now added the feature<br /> so that tables with foreign columns can also be filled using this SP. <br />****************************************************************************************************************/<br /><br />CREATE PROCEDURE USP_FillTable<br />(<br /> @TableName varchar(256)<br /> ,@RowCount int = 100<br /> ,@FillType varchar(20) = 'Random'<br />)<br />AS<br /><br />SET NOCOUNT OFF<br /><br />-- Declaring procedure variables<br />DECLARE @Columns varchar(1000)<br /> ,@Values varchar(7000)<br /> ,@intValues varchar(1500)<br /> ,@fltValues varchar(1500) <br /> ,@vcrValues varchar(4000) <br /> ,@dtValues varchar(500)<br /> ,@Query varchar(8000) <br /> ,@BigText varchar(8000)<br /> ,@GroupDataType varchar(100)<br /> ,@DataType varchar(100)<br /> ,@DyanSQL nvarchar(4000)<br /> ,@DyanParam nvarchar(4000)<br /> ,@i int<br /> ,@j int <br /> ,@si int<br /> ,@ti int <br /> ,@RandomNumber int <br /> ,@Rows int <br /> ,@Length int <br /> ,@min int <br /> ,@RandNum int <br /> ,@Variable1 int <br />-- Variables for Foreign Key<br /> ,@fkeyColName varchar(50)<br /> ,@fkeyTabName varchar(50)<br /> ,@IsForeignKey bit<br /> ,@fkeyRowCount int<br /> ,@fkeyRowNo int<br /> ,@fkeyvalues varchar(4000)<br /> ,@fkeyvalue varchar(4000) <br />-- Declaring error handling values<br /> ,@ErrMsg varchar(250)<br /> ,@Action varchar(20)<br /> ,@Object varchar(20)<br /> ,@Type varchar(20)<br /> ,@Error int<br /><br />-- Initializing values<br />SELECT @RowCount = coalesce(@RowCount, 100)<br /> ,@Columns = ''<br /> ,@BigText= 'Microsoft SQL Server 2000 includes several components you can use to build data warehouses that effectively support your decision support processing needs. Data Warehousing Framework The Data Warehousing Framework is a set of components and APIs that implement the data warehousing features of SQL Server 2000. It provides a common interface to be used by various components seeking to build and use a data warehouse or data mart. Data Transformation Services Data Transformation Services (DTS) provides a set of services used to build a data warehouse or data mart. Decision support systems analyze data to find trends of interest to the database users. Online transaction processing databases store large numbers of records covering the details of each transaction, and online analytical processing (OLAP) systems aggregate and summarize the information to speed analysis of the trends exhibited in the data. DTS offers support for extracting data from heterogeneous OLE DB data sources and the summarizing or aggregating of data to build a data warehouse. Online Analytical Processing Support Microsoft SQL Server 2000 Analysis Services allows you to build flexible, powerful business intelligence applications for Web sites and large enterprise systems. Microsoft SQL Server 2000 Analysis Services provides OLAP processing capabilities against heterogeneous OLE DB data sources. It has efficient algorithms for defining and building multidimensional cubes that can be referenced by applications using the OLE DB 2.0 OLAP extensions or the Microsoft ActiveX® Data Objects Multidimensional extensions. Analysis Services is an excellent tool for multidimensional analysis of data in SQL Server 2000 databases. Analysis Services supports multidimensional queries against cubes with hundreds of millions of dimensions. You can control cube security down to the level of cells and members. You can create custom rollup functions that tailor the types of aggregations and processing that can be performed in multidimensional cubes. <br /> Data Mining Support Data mining allows you to define models containing grouping and predictive rules that can be applied to data in either a relational database or multi-dimensional OLAP cubes. These predictive models are then used to automatically perform sophisticated analysis of the data to find trends that help you identify new opportunities and chose the ones that have a winning outcome. SQL Server 2000 Analysis Services includes support for data mining models, including API support of the OLE DB for Data Mining specification. Through the OLE DB for Data Mining API, Analysis Services supports integration with third-party data mining providers. English Query English Query makes a definition of the entities and relationships defined in a SQL Server 2000 database. Given this definition, an application can use an Automation API to pass English Query a string containing a natural-language question about the data in the database. English Query returns a SQL statement that the application can use to extract the necessary data. Meta Data Services SQL Server 2000 includes Microsoft Meta Data Services, which consists of a set of Microsoft ActiveX® interfaces and information models that define database schema and data transformations as defined by the Microsoft Data Warehousing Framework. A goal of the Microsoft Data Warehousing Framework is to provide meaningful integration of multiple products through shared meta data. It combines business and technical meta data to provide an industry standard method for storing the schema of production data sources and destinations.<br /> Meta Data Services is the preferred means of storing DTS packages in a data warehousing scenario because it is the only method of providing data lineage for packages. DTS also uses Meta Data Services storage to allow transformations, queries, and ActiveX scripts to be reused by heterogeneous applications.'<br /> ,@RandNum = round(0 + (rand() * (100)),0)<br /><br />-- 1) Get all the columns and their datatype for the given table<br />IF object_id('tempdb..#Columns') IS NOT NULL DROP TABLE #Columns<br />SELECT Identity(int,1,1) AS IdentityColumn<br /> ,Column_Name AS ColumnName <br /> ,Data_Type AS DataType <br /> ,Data_Type AS GroupDataType <br /> ,Character_Maximum_length AS Length <br /> ,IsForeignKey = 1<br /> INTO #Columns <br /> FROM Information_Schema.Columns <br /> WHERE 1 = 2 <br /><br />SELECT @Rows = @@rowcount, @Action = 'Creating', @Object = '#Columns', @Type = 'Table', @Error = @@error<br />IF @Error &lt;&gt; 0 GOTO Crash <br /><br />INSERT INTO #Columns<br />SELECT Column_Name AS ColumnName <br /> ,Data_Type AS DataType <br /> ,GroupDataType = CASE Data_Type WHEN 'float' THEN 'real'<br /> WHEN 'real' THEN 'real' <br /> WHEN 'numeric' THEN 'real' <br /> WHEN 'decimal' THEN 'real' <br /> WHEN 'smallmoney' THEN 'real'<br /> WHEN 'money' THEN 'real'<br /> WHEN 'bigint' THEN 'int'<br /> WHEN 'int' THEN 'int'<br /> WHEN 'Smallint' THEN 'int'<br /> WHEN 'tinyint' THEN 'int'<br /> WHEN 'bit' THEN 'int' <br /> WHEN 'char' THEN 'varchar'<br /> WHEN 'nchar' THEN 'varchar'<br /> WHEN 'ntext' THEN 'varchar'<br /> WHEN 'nvarchar' THEN 'varchar'<br /> WHEN 'Varchar' THEN 'varchar'<br /> WHEN 'text' THEN 'varchar' <br /> WHEN 'datetime' THEN 'datetime' <br /> WHEN 'smalldatetime' THEN 'datetime' <br /> ELSE 'varchar' <br /> END<br /> ,Character_Maximum_Length As Length<br /> ,IsForeignKey = CASE WHEN Column_Name IN ((SELECT name <br /> FROM SYSCONSTRAINTS B <br /> INNER JOIN SYSCOLUMNS A <br /> ON object_name(A.id) = object_name(B.id)<br /> AND B.colid = A.colid<br /> AND object_name(A.id) = @TableName))<br /> THEN 1<br /> ELSE 0<br /> END <br /> FROM Information_Schema.Columns <br /> WHERE Table_Name = @TableName <br /> AND Data_Type NOT IN ('sql_variant','timestamp','uniqueidentifier','image','binary','varbinary')<br /> AND NOT EXISTS (SELECT 1 FROM syscolumns <br /> WHERE id = object_id(@TableName)<br /> AND Name = Column_Name<br /> AND Colstat IN (1,4)) <br /> ORDER BY IsForeignKey<br /> ,GroupDataType<br /> ,DataType<br /> <br />SELECT @Rows = @@rowcount, @Action = 'Inserting', @Object = '#Columns', @Type = 'Table'<br />IF @Rows = 0 <br />BEGIN<br />RAISERROR('Insert Failed. Specified a TableName that is either a system table or doesn''t exist',18,126)<br />return<br />END<br />IF @error &lt;&gt; 0 GOTO Crash <br /><br />-- 2) Assign the column names into a single variable<br />SELECT @i = 1<br />WHILE @i &lt;= @Rows <br />BEGIN<br /> SELECT @Columns = @columns + ColumnName + ',' FROM #Columns WHERE IdentityColumn = @i<br /> SELECT @i = @i + 1<br />END<br />SELECT @Columns = left(@Columns,len(@Columns)-1)<br /><br />SELECT @i = 1 -- for filling increment values<br /> ,@si = 1<br /> ,@ti = 1<br /><br />-- Total number of values to be inserted<br />WHILE @RowCount &gt; 0<br />BEGIN<br /> SELECT @si = CASE WHEN @si = 32766 THEN 1 ELSE @si END<br /> ,@ti = CASE WHEN @ti = 127 THEN 1 ELSE @ti END<br /> -- 1) Assigning Values that should hold its value till the while loop<br /> SELECT @RandomNumber = round(0 + (rand() * (50-0)),0)<br /> ,@Values = ''<br /> ,@intValues = ''<br /> ,@fltValues = ''<br /> ,@vcrValues = ''<br /> ,@dtValues = ''<br /> ,@fkeyvalues = ''<br /> ,@Query = ''<br /> ,@j = 1<br /><br /> -- 2) Assign Random Values to the Columns<br /> WHILE @j &lt;= @Rows <br /> BEGIN<br /> SELECT @Variable1 = 135<br /> SELECT @GroupDataType = GroupDataType <br /> ,@Datatype = DataType <br /> ,@Length = Length <br /> ,@IsForeignKey = IsForeignKey<br /> FROM #Columns <br /> WHERE IdentityColumn = @j<br /> SELECT @Action = 'Selecting', @Object = '#Columns', @Type = 'Table',@error = @@error<br /> IF @error &lt;&gt; 0 GOTO Crash <br /><br /> IF @IsForeignKey = 0<br /> BEGIN<br /> IF @GroupDataType = 'int'<br /> BEGIN<br /> IF @FillType = 'Increment'<br /> BEGIN<br /> SELECT @intValues = @intValues <br /> + convert(varchar,CASE @DataType WHEN 'bigint' THEN ((@RandNum + @i) + (@j*@Variable1))<br /> WHEN 'int' THEN ((@RandNum + @i) + (@j*@Variable1))<br /> WHEN 'Smallint' THEN @si -- added to prevent SP failing from arith-overflow-errors<br /> WHEN 'tinyint' THEN @ti -- added to prevent SP failing from arith-overflow-errors<br /> WHEN 'bit' THEN (CASE WHEN right(convert(int,rand(@RandNum)),1) &gt; 5 THEN 1 ELSE 0 END)<br /> ELSE 0<br /> END)<br /> + '9848265374' <br /> END<br /> ELSE<br /> BEGIN<br /> SELECT @intValues = @intValues <br /> + convert(varchar,CASE @DataType WHEN 'bigint' THEN @RandomNumber * round(0 + (rand() * (100-0)),0)<br /> WHEN 'int' THEN @RandomNumber * round(0 + (rand() * (50-0)),0)<br /> WHEN 'Smallint' THEN @RandomNumber * round(0 + (rand() * (20-0)),0)<br /> WHEN 'tinyint' THEN @RandomNumber * round(0 + (rand()),0)<br /> WHEN 'bit' THEN (CASE WHEN right(convert(int,rand(@RandomNumber)),1) &gt; 5 THEN 1 ELSE 0 END)<br /> ELSE 0<br /> END)<br /> + '9848265374' <br /> END <br /> END -- End of int IF<br /> ELSE IF @GroupDataType = 'real'<br /> BEGIN <br /> SELECT @fltValues = @fltValues <br /> + convert(varchar,CASE @DataType WHEN 'decimal' THEN convert(real,(rand() * 1000))<br /> WHEN 'float' THEN convert(real,(rand() * 1000))<br /> WHEN 'numeric' THEN convert(real,(rand() * 1000))<br /> WHEN 'real' THEN convert(real,(rand() * 1000))<br /> WHEN 'money' THEN convert(real,(rand() * 1000))<br /> WHEN 'smallmoney' THEN convert(real,(rand() * 1000))<br /> ELSE 0.0<br /> END)<br /> + '9848265374' <br /> END -- End of real IF <br /> ELSE IF @GroupDataType = 'varchar'<br /> BEGIN <br /> SELECT @vcrvalues = @vcrValues <br /> + '''' <br /> + CASE @DataType WHEN 'char' THEN left(substring(@BigText,convert(int,rand()*25),convert(int,rand()*35)),@length) <br /> WHEN 'nchar' THEN left(substring(@BigText,convert(int,rand()*550),convert(int,rand()*35)),@length) <br /> WHEN 'ntext' THEN left(substring(@BigText,convert(int,rand()*125),convert(int,rand()*100)),@length) <br /> WHEN 'nvarchar' THEN left(substring(@BigText,convert(int,rand()*200),convert(int,rand()*35)),@length) <br /> WHEN 'varchar' THEN left(substring(@BigText,convert(int,rand()*300),convert(int,rand()*35)),@length) <br /> WHEN 'text' THEN left(substring(@BigText,convert(int,rand()*250),convert(int,rand()*1000)),@length) <br /> ELSE left(substring(@BigText,convert(int,rand()*250),convert(int,rand()*100)),@length) <br /> END <br /> + '''' <br /> + ',' <br /> END -- End of varchar IF<br /> ELSE IF @GroupDataType = 'datetime'<br /> BEGIN <br /> SELECT @dtvalues = @dtValues <br /> + '''' <br /> + CASE @DataType WHEN 'datetime' THEN convert(varchar,getdate()- @RandomNumber)<br /> ELSE convert(varchar,convert(smalldatetime,getdate()- @RandomNumber))<br /> END <br /> + '''' <br /> + ',' <br /> END -- End of datetime IF<br /> END<br /> ELSE <br /> BEGIN<br /> SELECT @fkeyColName = name<br /> ,@fkeyTabName = object_name(a.id) <br /> FROM syscolumns A <br /> INNER JOIN sysforeignkeys B<br /> ON object_name(a.id) = object_name(b.rkeyid)<br /> AND b.rkey = a.colid<br /> WHERE object_name(b.fkeyid) = @TableName<br /><br /> SELECT @DyanSQL = 'SELECT @fkeyRowCount1 = count(*) FROM '+ @fkeyTabName<br /> SELECT @DyanParam = '@fkeyRowCount1 int OUTPUT'<br /> EXEC sp_executesql @DyanSQL, @DyanParam, @fkeyRowCount1 = @fkeyRowCount OUTPUT<br /><br /> SELECT @fkeyRowNo = round(0 + (rand() * ((@fkeyRowCount-1)-0)),0) <br /><br /> SELECT @DyanSQL = 'SELECT TOP 1 @fkeyvalue1 = ' + @fkeyColName + ' <br /> FROM ( SELECT TOP ' + convert(varchar(10),isnull(@fkeyRowNo,0)) + ' ' + @fkeyColName <br /> + ' FROM ' + @fkeyTabName + ' ORDER BY ' + @fkeyColName + ') SubQuery <br /> ORDER BY ' + @fkeyColName + ' DESC'<br /> SELECT @DyanParam = '@fkeyvalue1 varchar(200) OUTPUT'<br /> EXEC sp_executesql @DyanSQL, @DyanParam, @fkeyvalue1 = @fkeyvalue OUTPUT<br /><br /> SELECT @fkeyvalues = @fkeyvalues + @fkeyvalue + '9848265374' <br /> END<br /> SELECT @j = @j + 1<br /> END<br /><br /> -- 4) Consolidating all the values into a single variable <br /> SELECT @intValues = replace(@intValues,'9848265374',',')<br /> ,@fltValues = replace(@fltValues,'9848265374',',')<br /> ,@fkeyvalues = replace(@fkeyvalues,'9848265374',',')<br /> ,@Values = left(@dtValues + @intValues + @fltValues + @vcrValues + @fkeyvalues , len(@intValues + @fltValues + @vcrValues + @dtValues + @fkeyvalues)-1)<br /> ,@Query = 'INSERT INTO ' + @TableName + '(' + @Columns + ') values(' + @Values + ')' <br /><br /> -- 5) Filling values into the target table<br /> SET NOCOUNT OFF<br /> EXEC (@query) <br /> SELECT @Action = 'Inserting', @Object = @TableName, @Type = 'Table',@error = @@error<br /> IF @error &lt;&gt; 0 GOTO Crash <br /> SET NOCOUNT ON<br /> <br /> SELECT @i = @i + 1<br /> ,@si = @si + 1<br /> ,@ti = @ti + 1<br /> SELECT @RowCount = @RowCount - 1<br />END -- Rowcount end<br />RETURN<br /><br />Crash:<br /><br />SELECT @ErrMsg = 'Error ' + @Action + ' ' + @Type + ' ' + @Object<br />RAISERROR(@ErrMsg,18,126)<br />RETURN -200<br /><br />Go<br />IF @@error = 0 <br />PRINT 'Procedure USP_FillTable created'<br />Go<br /><br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Share This Page