10万件のテストデータの生成方法(SQL Server)

1. はじめに

ATLシステムズの田中です～。
SQLの勉強のために、DB(データベース)に数万件の大量のレコードを入れてみたいけど、大きなイニシャルデータの自作の仕方がわからない！Excelよりもっと大量のレコードを簡単に生成したい！
と、思ったことありますか？

経験がある方は挙手してください。・・・　(-ω-;)/　「はい！」
挙手されたら、読み進めてください（笑）

DBは、大量のレコードを入れてクエリを実行してみないと、本当の挙動を見ることはできません。そこで、最初に入れるテストデータを簡単に作れる方法の必要性を感じたので、本記事でそのうちの一つを紹介しようと思います。

※免責事項(必ず、ご一読ください)
　　本記事の情報により生じたいかなる損害や損失についても、当社は一切の責任を負いかねます。
　　また、誤情報が入り込んだり、情報が古くなったりすることもありますので、必ずしも正確性を保証するものではありませんのでご了承ください。

2. 概要

　SQL Databaseにcsvのデータをインポートして、レコードをSQLクエリを実行して増やします。

3. 結果

10万件のテストデータを生成することができました。方法は以降の章に記載しました。
なお、種テーブルは疑似個人情報生成サービスから生成したものです。　　
6.テーブル2の作成を実行すると、種テーブルから10万件のレコードを生成します。

今回はSQL Serverに特化した書き方となっていますが、他のDBのSQLでも、同じような方法で実現できると思います。

4. データの用意

種データはこちらのWebサービスから生成できます。とても便利です。
　　疑似個人情報生成サービス

5. テーブル1(種テーブル)の作成

　SSMS(SQL Server Management Studio)から、上記で作成したcsvを取り込みます。詳しい取り込み方は、
こちらの方が詳しく解説して下さっておりますので、ご参考にしてみてください。
→【SQL Server】Management Studioで簡単に大量データを挿入する方法

　　入口：DBを右クリック→[タスク]→[データのエクスポート]を左クリック

　ウィザードを進めていくと、このcsvを取り込めるテーブルをウィザードの中で作っていけます。
　ここで作ったテーブルをテーブル1(種テーブル)とします。
　※ウィザード内で列削除のようなことができますが、データの出力が列方向にずれることがあるので要注意です。

6. テーブル2の作成

　　テーブル1には10行程度しかないため、私の作った SQLクエリでテストデータっぽく、テーブル1からランダムに1行取り出してはテーブル2に入れる処理をWHILE文で100000回繰り返します。（回数はindexをいじることで、変更可能）
実行するとこんな感じになり、テーブル2の中身に3. 結果が出力されます。

7. SSMSからのデータエクスポート

必要に応じて、DBのテーブル2からデータをエクスポートできます。
こちらの方が詳しく解説して下さっておりますので、ご参考にしてみてください。
→【SQL Server】SSMSでCSVをエクスポートする

8. 最後に

　最後まで読んでいただき、ありがとうございました！
　次回は、この記事で作ったテストデータを、jsonに変換して、Azure Cosmos DBに簡単なツールを使ってデータインジェストしてみます！
　余談ですが、弊社はfacebookもしております！
　　　　　　　　

9. 参考資料

• 疑似個人情報生成サービス
• 【SQL Server】Management Studioで簡単に大量データを挿入する方法
• 【SQL Server】SSMSでCSVをエクスポートする

10. 付録1

例：ランダムテストデータ生成.sql

use fsTestDB2
- テーブル名2の中身を全部捨てる
TRUNCATE table Table_2
-- キャッシュを消す
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--変数宣言
DECLARE @index INTEGER
DECLARE @seedsize INTEGER
DECLARE @STARTDATETIME datetime2 = SYSDATETIME()
--ループ用変数を初期化
SET @index = 1
select @seedsize = count([名前]) from Table_1
--エラーが起きたら以下のトランザクションをロールバック
SET XACT_ABORT ON;
BEGIN TRANSACTION;

WHILE @index <= 100000
BEGIN
--テーブル1をランダムなIDで降順にならべたときの先頭レコードだけ、テーブル2に入れる
    insert into dbo.Table_2
      select top(1) @index
      ,[名前]
      ,[地域名]
      ,[投票者]
      ,[登録年月日]
      ,[更新年月日]
      ,[削除フラグ] from Table_1 as s order by ABS(CAST((BINARY_CHECKSUM ([名前], NEWID())) as int))% @seedsize desc
    --ループ用変数をインクリメント
    SET @index = @index + 1
END
COMMIT TRANSACTION;