SQLServerで大量データの作成方法について

こんにちは！

久しぶりの投稿になります。

今回はとあるお客様への提案で月間１００万件を超えるデータをPowerBIで扱う必要があり、提案前の検証作業で大量のダミーデータを作成する必要があったのでその方法をご紹介したいと思います。

今まで私が扱ったPowerBIでの最大レコード数は１５万件ぐらいだったので、今回扱うデータ量は半端ない量になります。

PCのスペックにもよると思いますが、１５万件でもかなりPowerBIが重たくなったりしていたので事前にどれぐらいのデータ件数が扱えるか確認する必要がありました。

でいろいろしらべていたらPowerBIDesktopでは20億レコードまではインポートできるようです。

DirectQueryの場合は100万レコードまでのようです。

PowerBIDesktopの上限を確認できたのですが、実際にPowerBIに取り込んで可視化してレスポンスなど問題がないか事前に確認しないとちょっと心配だな〜と思いました。

そこで早速、WindowsPCにSQLServer2017Developer版をインストールしてサンプルDBをリストアしました！

でここからです。

大量にデータを作る必要があり、最初はエクセルでInsert文を作ろうかな？！と考えて実際に数千件をInsertしてみました！

ところがかなり遅く、これを１億件ダミーデータ作るとなると何時間かかるんだろうと思い、別の方法を探しました！

そうすると以下のURLでかなり参考になる記事があったので真似てみました！

http://d.hatena.ne.jp/dotnetmemo/20111015/1318663995

実際に実行したスクリプトの例が以下です。（以下のスクリプトは上記のURLに記載のスクリプトになります）

Declare @p_NumberOfRows Bigint
Select @p_NumberOfRows=1000000;
With Base As
(
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
),
Expand As
(
Select 1 as C From Base as B1, Base as B2
),
Nums As
(
Select Row_Number() OVER(ORDER BY C) As n From Expand
)
INSERT INTO TestTable
Select n, ‘DATA’ + right(‘0000000000’ + convert(varchar, n), 10)
from Nums Where n<=@p_NumberOfRows
OPTION (MaxRecursion 0);