4

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.

Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206

1 Answers1

8

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).

Michael Goldshteyn
  • 71,784
  • 24
  • 131
  • 181
  • Thanks, this is what I was looking for. It took me 33 seconds :-) Anyways, I will try to do the same on other tables with more column. – Zerotoinfinity Apr 26 '12 at 14:45
  • 1
    Perhaps I should have explicitly stated that my PC is an overclocked 4.7 GHz i7-2600k with an Intel 320 SSD for storage when giving you that timing example. If you would like more columns, just replace `B1.*` with `*` and you will have twice as many columns (i.e., all of `B1`'s columns and all of `B2`'s columns). – Michael Goldshteyn Apr 26 '12 at 14:46