Entering random data into a table

Junior Galvão - MVP, 2018-06-14 (first published: 2018-06-12)

This block of code is intended to demonstrate how we can in Microsoft SQL Server from version 2008. Use any user database to perform a execution of this code block and table storage RandomDataTable.
You can use this block of code to test and study environments, with the purpose of generating and populate a table with random data.
These random data are produced using the 130 characters declared in the @Texto variable, during command execution While.
The variable @Posicao is used to control and position the use of characters declared in the @Texto variable.
The @RowCount variable has the function to control the number of rows stored during execution with comado While.
After the amount of random rows being inserted the Select command should display the mass of data created and inserted into the RandomDataTabela table.
    Create Table RandomDataTable
  (ID int IDENTITY(1,1) NOT NULL Primary Key,
   CustomerID int NOT NULL,
   SalesPersonID varchar(10) NOT NULL,
   Quantity smallint NOT NULL,
   NumericValue numeric(18, 2) NOT NULL,
   Today date NOT NULL)
Go

--Inserting the data mass into the RandomDataTable --
Declare @Texto Char(130), 
        @Posicao TinyInt, 
		@RowCount Int

Set @Texto = '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ\_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ' --There are 130 characters in this text--

Set @RowCount = Rand()*100000 -- Set the amount of lines to be inserted --

While (@RowCount >=1)
Begin

 Set @Posicao=Rand()*130

 If @Posicao <=125
  Begin
   Insert Into RandomDataTable (CustomerID, SalesPersonID, Quantity, NumericValue, Today)
   Values(@RowCount, 
                 Concat(SubString(@Texto,@Posicao+2,2),SubString(@Texto,@Posicao-4,4),SubString(@Texto,@Posicao+2,4)),
                 Rand()*1000, 
	             Rand()*100+5, 
	             DATEADD(d, 1000*Rand() ,GetDate()))
  End
  Else
  Begin
    Insert Into RandomDataTable (CustomerID, SalesPersonID, Quantity, NumericValue, Today)
    Values(@RowCount, 
                  Concat(SubString(@Texto,@Posicao-10,1),SubString(@Texto,@Posicao+4,6),SubString(@Texto,@Posicao-12,3)),
                  Rand()*1000, 
	              Rand()*100+5, 
	              DATEADD(d, 1000*Rand() ,GetDate()))
   End

   Set @RowCount = @RowCount - 1
End

Select ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today 
From RandomDataTable
Go

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)

Related content