How to insert Huge dummy data to Sql server

Question

Currently development team is done their application, and as a tester needs to insert 1000000 records into the 20 tables, for performance testing.

I gone through the tables and there is relationship between all the tables actually.

To insert that much dummy data into the tables, I need to understand the application completely in very short span so that I don't have the dummy data also by this time.

In SQL server is there any way to insert this much data insertion possibility.

please share the approaches.

Currently I am planning with the possibilities to create dummy data in excel, but here I am not sure the relationships between the tables.
Found in Google that SQL profiler will provide the order of execution, but waiting for the access to analyze this.
One more thing I found in Google is red-gate tool can be used.

Is there any script or any other solution to perform this tasks in simple way.

I am very sorry if this is a common question, I am working first time in SQL real time scenario. but I have the knowledge on SQL.

@yog241: its a bulk insert but data has to genarate automatically — Chaitanya Phani, Feb 12 '15 at 15:46
Depending on how much time and knowledge you have I would either create a unit test method which is calling same apis of the application to create new entities and do that in a loop for your 1.000.000 times, or I would use SQL Profiler to capture the inserts generated by the application and then make those inserts abstract from IDs and then put those inserts in a while loop in SQL Server. I have also been searching for such data population tool in the past and I think I did find something online but not sure how it works with complex db schemas having lots of relationships between tables... — Davide Piras, Feb 12 '15 at 15:50
Have you look at https://msdn.microsoft.com/en-us/library/aa833267(v=vs.100).aspx and at https://msdn.microsoft.com/en-us/library/dd193262(v=vs.100).aspx. Data generation tool is part of Database Projects in Visual Studio 2010 — Jesús López, Feb 12 '15 at 16:42
@jesus: seems this tool is looking good, but in vs professional it is not possible. — Chaitanya Phani, Feb 15 '15 at 08:52
tools is really good, but client needs some realistic data..but thanks for the above tool Jesus, great learning for me. — Chaitanya Phani, Feb 19 '15 at 13:18

score 14 · Accepted Answer · answered Mar 09 '15 at 09:45

Why You don't generate those records in SQL Server. Here is a script to generate table with 1000000 rows:

DECLARE @values TABLE (DataValue int, RandValue INT)

;WITH mycte AS
(
SELECT 1 DataValue
UNION all
SELECT DataValue + 1
FROM    mycte   
WHERE   DataValue + 1 <= 1000000
)
INSERT INTO @values(DataValue,RandValue)
SELECT 
        DataValue,
        convert(int, convert (varbinary(4), NEWID(), 1)) AS RandValue
FROM mycte m 
OPTION (MAXRECURSION 0)


SELECT 
        v.DataValue,
        v.RandValue,
        (SELECT TOP 1 [User_ID] FROM tblUsers ORDER BY NEWID())
FROM    @values v

In table @values You will have some random int value(column RandValue) which can be used to generate values for other columns. Also You have example of getting random foreign key.

score 0 · Answer 2 · answered Mar 17 '16 at 09:12

Below is a simple procedure I wrote to insert millions of dummy records into the table, I know its not the most efficient one but serves the purpose for a million records it takes around 5 minutes. You need to pass the no of records you need to generate while executing the procedure.

IF  EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DUMMY_INSERT]') AND type in (N'P', N'PC'))
BEGIN
    DROP PROCEDURE  DUMMY_INSERT
END
GO
CREATE PROCEDURE DUMMY_INSERT (
@noOfRecords INT
)
AS
BEGIN

DECLARE @count int
SET @count = 1;

WHILE (@count < @noOfRecords)
BEGIN
    INSERT INTO [dbo].[LogTable] ([UserId],[UserName],[Priority],[CmdName],[Message],[Success],[StartTime],[EndTime],[RemoteAddress],[TId])
     VALUES(1,'user_'+CAST(@count AS VARCHAR(256)),1,'dummy command','dummy message.',0,convert(varchar(50),dateadd(D,Round(RAND() * 1000,1),getdate()),121),convert(varchar(50),dateadd(D,Round(RAND() * 1000,1),getdate()),121),'160.200.45.1',1);

     SET @count = @count + 1;
END
END

score 0 · Answer 3 · answered Apr 09 '19 at 11:41

you can use the cursor for repeat data:

for example this simple code:

Declare @SYMBOL nchar(255), --sample V
         @SY_ID     int         --sample V
Declare R2 Cursor
    For SELECT  [ColumnsName]
        FROM    [TableName]
    For Read Only;
Open R2
Fetch  Next From R2 INTO @SYMBOL,@SY_ID
    While (@@FETCH_STATUS <>-1 )
        Begin
            Insert INTO [TableName] ([ColumnsName])
                Values              (@SYMBOL,@SY_ID)
            Fetch  Next From R2 INTO @SYMBOL,@SY_ID
        End
Close R2
Deallocate R2
/*wait a ... moment*/
SELECT COUNT(*)                  --check result
        FROM [TableName]

How to insert Huge dummy data to Sql server

3 Answers3

Linked