Recently I had to do some R&D for a project and needed to generate a large table to test performance on. Since it is important to test queries on large data sets I thought that I would post how I populated a few data types in my table. To test query results I decided that I needed some values in the table that were not random so I simply salted my random data with known data at irregular intervals that would allow for testing. With a little bit of work I ended up with over 4 million records in a table with 45 columns. You can take the code below and can expand it to fit your needs. Just change the value for the @Loop variable to control how many records you want to insert into your table.
CREATE TABLE RandomLoad ([NumberColumn] int,[BitColumn] bit,[VarcharColumn] varchar(20),[CharColumn] char(1),[DateColumn] date)
*************************
–The below code will populate the RandomLoad table with random records
DECLARE @Loop INT, @Num1 INT, @Length INT
DECLARE @Varchar1 VARCHAR(20)
DECLARE @Varbin1 VARBINARY(128)
DECLARE @Char1 CHAR(2)
DECLARE @Bit1 BIT
DECLARE @Date1 DATE
SET @Loop = 0
WHILE @Loop < 1000 — number of records to generate
BEGIN
— Generate Number
SET @Num1 = ROUND(RAND() * 10000, 0)
— Generate Bit
SET @Bit1 = CRYPT_GEN_RANDOM(1)%2
— Generate Varchar
SET @Varchar1 = ”
SET @Length = CAST(RAND() * 20 AS INT) — Up to 20 characters long
WHILE @Length <> 0
BEGIN
SET @Varchar1 = @Varchar1 + CHAR(CAST(RAND() * 96 + 32 AS INT))
SET @Length = @Length – 1
END
— Generate Char
SET @Char1 = LEFT(newid(),1)
— Generate Date
SET @Date1 = CAST(GETDATE() + (365 * 2 * RAND() – 365) AS DATE)
INSERT INTO [RandomLoad] VALUES (@Num1,@Bit1,@Varchar1,@Char1,@Date1)
SET @Loop = @Loop + 1
END
GO
********************************************
Need more info or have questions please comment or contact us at SQLRxSupport@sqlrx.com and we will be happy to help!