Query to get 5 million records from Adventure works

Question

I am generating a flat file from SQL Server and I want to have around 3-5 million records for that. Can someone give me a query which I can run on Adventure works to get 3-5 million records? I am looking from adventure works because it has meaningful data.

Answer 1

The following query will give you what you are looking for, 3 to 5 million rows from the Adventure Works database based on the value in the variable @NumRows:

DECLARE @NumRows INT=3000000; -- Number of rows to return

SELECT TOP(@NumRows) B1.*                -- Take @NumRows out of:
FROM Production.BillOfMaterials B1       -- BillOfMaterials has 2,679 rows
CROSS JOIN Production.BillOfMaterials B2 -- multiplied by 2,679 rows = 7,177,041 rows

Note: This query may take a while to run (e.g., 17 seconds on my PC).