SQL Serverでデータを増幅する方法

この記事では、SQL Serverで構築されたデータベース上において、データ（レコード）を増幅する方法を解説します。

大量データを用いた検証を行う場合に、Excelにデータ内容を記載して、関数を使って一件ずつINSERT文を生成し、それらを全部実行する、といったような方法をとっていませんか？
もちろんその方法でも大量データを作成することは可能ですが、同じような内容が書かれたINSERT文を大量に生成するのは、あまりスマートな方法とは言えません。

以降で紹介する方法を使用することで、既存のデータをもとに必要な項目だけを変更した大量データを、比較的短いコードで生成することができます。

当記事では、無名ブロックを使用する方法と、再帰問い合わせを使用する方法の2つを取り上げますが、どちらの方法を用いても、大量データ（レコード）の増幅が可能です。
それぞれの方法について、コピペと簡単な修正だけで使用できるコード例を記載していますので、ぜひ活用してみてください。

方法１｜無名ブロックを使用する方法

ひとつめの方法は「無名ブロック」を使用する方法です。

無名ブロックとは、JavaやPythonなどのプログラミング言語と同じように、順次処理ができる機能のことです。

データベースを操作する言語といえばSQLですが、SQLはその名の通りクエリを記述する言語なので、繰り返しや条件分岐などを含む順次処理を記述することができません。

通常データベースを扱う順次処理を記述する際は、JavaやPythonなどの言語を用いて、データベース接続→データ取得→データ加工→更新結果をデータベースに反映、といったような処理を記述することになります。

このような処理では、データベースの外にあるプログラムがデータを扱うことになりますが、無名ブロックを使用することで、データベース内で順次処理を行うことができるのです。

コード例

それでは、具体的なコード例を見て見ましょう。

DECLARE @cnt INT
SET @cnt = 0
WHILE @cnt < 100
  INSERT INTO TABLE_NAME
  SELECT
    @cnt + 2 AS P_KEY
    ,COL1
    ,COL2
  FROM TABLE_NAME
  WHERE P_KEY = 1
  SET @cnt = @cnt + 1
LOOP
;

コピペで使用する場合は、以下の点を変更して使用してください。

• INSERT INTO 直後とFROM句にある「TABLE_NAME」を実際のテーブル名に変更する。
• SELECT句の内容を、増幅するレコードの内容に合わせて修正する。
• WHERE句の抽出条件を、コピー元のレコードを特定できる条件に修正する。
• WHILE直後のループ回数を増幅したいレコード数に修正する。
  

コード解説

ここから、上記のコードで何が行われるのかを解説します。

まず、全体の構造を把握するために、一部省略した形でコードを再掲します。

DECLARE @cnt INT
SET @cnt = 0
WHILE @cnt < 100
  ~ SELECT INSERT 句 ~
  SET @cnt = @cnt + 1
LOOP
;

SELECT INSERT 句の部分を「~」で囲み、内容を省略しました。

最初の行のDECLARE構文は変数の定義を行なうための命令で、@cntという名前でINT型の変数を定義しています。
次のSET構文は変数に値を代入するための構文で、@cnt変数に0という値を代入しています。
なお、SQL Serverの変数名には冒頭に「@」をつけるルールがありますので、ここでは@cntという名前にしています。

WHILEからLOOPまでが繰り返し処理となっていて、@cntが100を下回る限り処理を行い続けるよう記述されています。
LOOPの一行上にあるSET命令によって@cntが1ずつ加算されていきますので、この処理は@cntが0から99になるまでの間、合計100回ループすることになります。

続いて、SELECT INSERT句を見ていきましょう。

INSERT INTO TABLE_NAME
SELECT
  @cnt + 2 AS P_KEY
  ,COL1
  ,COL2
FROM TABLE_NAME
WHERE P_KEY = 1

コピー元となるレコードを抽出できるようFROM句とWHERE句を指定し、コピー先のレコード内容をSELECT句で指定しています。

SELECT句では、元のレコードの内容をそのまま使用するカラムについては、元のカラム名をそのまま指定すればOKです。
連番を振りたい場合には、「@cnt + 2」の部分のように、ループカウンタである@cntを使用する方法があります。

INSERT INTO句でFROM句と同じテーブル名を指定することで、同じテーブル内でレコードを増幅することができます。

方法２｜再帰問い合わせを使用する方法

ふたつ目の方法は、再帰問い合わせを使用する方法です。

再帰問い合わせというのは、名前の通り、再帰的に呼び出される問い合わせのことです。
再帰的、というのは、繰り返し自分自身を呼び出す、といったようなイメージです。

繰り返し自分自身を呼び出すというのが、ある種ループに近い性質を持っており、それを利用することでレコード増幅を行うことができます。

コード例

まずは、コード例を見てみましょう。

WITH Base AS (
  SELECT 1 AS num
  UNION ALL
  SELECT num + 1 AS num FROM Base WHERE num <= 500
)
INSERT INTO TABLE_NAME
SELECT
    num + 1 AS P_KEY
    ,COL1
    ,COL2
FROM TABLE_NAME
INNER JOIN Base ON 1 = 1
WHERE P_KEY = 1
OPTION (Maxrecursion 500)
;

コピペで使用する場合は、以下の点を変更して使用してください。

• FROM句にある「TABLE_NAME」を実際のテーブル名に変更する。
• SELECT句の内容を、増幅するレコードの内容に合わせて修正する。
• WHERE句の抽出条件を、コピー元のレコードを特定できる条件に修正する。
• WITH句の中と、OPTIONのカッコ内にある「500」を増幅したいレコード数に修正する。
  

コード解説

ここから、コードの具体的な処理内容を解説します。

先頭にあるWITH句は、同じSQLの中だけで使用できる表を一時的に定義するための構文です。
WITHの直後にある「Base」がこの一時的に作成される表に付けられる名前であり、
ASの後にあるカッコの内容が表の定義になります。

WITH句のなかのSQLでは、2つのSQLをUNION ALLで連結しています。
上の「SELECT 1 AS num」は、numという名前のカラムで「1」という値を1行返すSQLになっています。
OracleのDBをご存知の方であれば、「SELECT 1 AS num FROM dual」と同じと考えればわかりやすいかもしれません。

下の「SELECT num + 1 AS num FROM Base WHERE num <= 500」では、FROM句にBaseが指定されています。
ここが再帰問い合わせを使用している部分で、Baseとして定義された表の中で、Base自身を呼び出すような構造になっています。
SELECT句に「num + 1」、WHERE句に「num <= 500」と指定することで、この再帰的な呼び出しは、nが500になるまで続けられることになります。
結果として、1から500までの値がnumカラムに格納された500件のレコードセットが得られます。

WITH句の後のSQLでは、WITH句で定義した「Base」とデータ増幅を行うテーブル「TABLE_NAME」を結合しています。
結合条件を「1 = 1」という必ずTRUEになる条件とすることで、「TABLE_NAME」のコピー元のレコードを「Base」にあるすべてのレコードと結合することができ、これによりデータが増幅できるようになります。

SELECT句、FROM句、WHERE句の指定方法は、方法１の無名ブロックを使用する方法と大きく変わりません。
連番を振る際に使用している変数が「Base」内で定義したカラム「num」になっている点を除けば、同じ条件になっています。

最後の行にある「OPTION (Maxrecursion 500)」は、再帰問い合わせの再帰回数を指定するためのオプションです。
再帰問い合わせでは、回数をきちんと指定しないと無限ループになる可能性があるため、SQL Serverのデフォルトの設定で、再帰回数の上限が100回までと決められています。
100回を超える繰り返しが必要となる場合は、上記のコード例のようにOPTION設定が必要となります。

おわりに

この記事では、SQL Serverで構築されたデータベース上で、大量データの増幅を行う方法について解説しました。

もとになるデータが既にデータベース上に存在する場合、上記の方法であればデータベース内で増幅作業を完結できるため、Excelにデータを貼り付けてコピペを行うよりも、より効率的と言えます。

記載しているコードには若干複雑な部分もあるかもしれませんが、一度身につけておくと役に立つ場面も多いかと思いますので、ぜひ活用してみてください。