Sql-server – How to fill/insert fake data into a MS Sql Server

datafilegroupssql serversql server 2014

I'm trying to test out some configuration settings for an MS Sql Server FILEGROUP and to do this I wish to fill my FILEGROUPdisk up so it will start populating the 2nd FILEGROUP.

I've just created a dummy table that on the PRIMARY FILEGROUP and have the following simple sql to insert some data. It's just taking so long and filling it slowly. Are there any better ways to quickly fill a table with junk data that really uses up a lot of hard disk space?

--IF OBJECT_ID('dbo.__RandomData', 'U') IS NOT NULL 
--BEGIN
--    DROP TABLE dbo.__RandomData; 

--    CREATE TABLE dbo.__RandomData ( 
--        RowId INT IDENTITY(1,1) NOT NULL, 
--        SomeInt INT,
--        SomeBit BIT, 
--        SomeVarchar VARCHAR(10), 
--        SomeDateTime DATETIME, 
--        SomeNumeric NUMERIC(16,2) ) 
--END
--GO

DECLARE @count INT 
SET @count = 1

WHILE @count <= 1000000
BEGIN
INSERT INTO dbo.__RandomData 
    SELECT    @count, 
            CASE WHEN DATEPART(MILLISECOND, GETDATE()) >= 500 THEN 0 ELSE 1 END [SomeBit], 
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
            CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) [SomeVarchar], 
            DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, 
                DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) [SomeDateTime], 
            (ABS(CHECKSUM(NEWID())) % 100001) + ((ABS(CHECKSUM(NEWID())) % 100001) * 0.00001) [SomeNumeric]
    SET @count += 1
END

Best Answer

Do a CROSS JOIN between sys.columns, use that same 2, 3 , or 4 times. Have a TOP, with as many rows as you want. Use that as a base for INSERT ... SELECT.

CREATE TABLE mytable(c1 int identity(1, 1), c2 char(10))

INSERT INTO mytable
SELECT TOP(1000) 'Hello'
FROM sys.columns AS a 
 CROSS JOIN sys.columns AS b 
 CROSS JOIN sys.columns AS c

The cross join is a classic trick. Do a cross join between two tables that has 1000 rows each and you have 1,000,000 rows in the result. With 3 tables, you have 1,000,000,000 rows. You can have some "numbers" table you create for these purposes, with for instance 1000 rows in it. Or, do a quick and dirty and use some view that you know exists with a decent amount or rows in it (like sys.columns).

Related Question