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.

1. Currently I am planning with the possibilities to create dummy data in excel, but here I am not sure the relationships between the tables.
2. Found in Google that SQL profiler will provide the order of execution, but waiting for the access to analyze this.
3. 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.

Answer 1

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.