先日投稿した記事のテーブルを使って、商品別売上実績のレコードが必ず存在するようにしてみます。
月末処理で売上平均金額を求める前に売上のない商品について0円の商品別売上実績レコードを作成する状況を想定しています。
ここではSELECT
した結果をINSERT
するINSERT SELECT
を使ってデータを登録します。
INSERT SELECTとは
INSERT SELECT
とは、INSERT
するデータをSELECT
で作成する手法のことです。例えば、テーブルAにテーブルBから抽出したレコードをそのままINSERT
する場合、以下のように書きます。
INSERT INTO A
SELECT
*
FROM
B
この例では、テーブルAとテーブルBは同じカラムを持っており、テーブルBから全件抽出してそのままテーブルAにINSERT
しています。このやり方はバックアップ対象と同じカラムを持つバックアップ用のテーブルを作成して、そこにテックアップ対象のレコードを全件抽出しておく、というときによく使います。
SELECT時に対象のカラムと行を絞り込む
今回実現したいのは、SELECT
するテーブルとINSERT
するテーブルのカラムは異なっています。これはSELECT
するカラムをINSERT
するカラムに合わせることで対応します。
もう一つ、すべてのカラムを抽出するのではなく、すでにINSERT
するテーブルに存在している商品コードはINSERT
する必要がない(INSERT
すると一意制約違反となる)ため、商品テーブルにあって商品別売上実績テーブルにない商品コードだけINSERT
するように対象レコードを絞り込みます。
作成したSQLは以下のようになります。
INSERT INTO sales_by_product
SELECT
T1.product_code,
0 total_sales_amount
FROM
products T1
WHERE
NOT EXISTS (
SELECT
1
FROM
sales_by_product T2
WHERE
T1.product_code = T2.product_code
);
INSERTするテーブルのカラムと同じカラムを抽出する
SELECT
ではINSERT
するテーブルと同じカラムを抽出します。
SELECT
T1.product_code,
0 total_sales_amount
FROM
products T1
INSERT
先の商品別売上実績テーブルはproduct_code
とtotal_sales_amount
を持っているため、これらのカラムだけになるように抽出しています。product_code
はproducts
テーブルのカラムを使用し、total_sales_amount
は今回やりたいことに合わせてデフォルト値の0
を設定するようにしています。0 total_sales_amount
のように固定値を直接記述することができるかどうかは使用しているRDBMSによって異なりますので注意してください。
INSERTするテーブルに存在していないレコードのみ抽出する
少しややこしいですが、NOT EXISTS
を使ってsales_by_product
に存在しない商品コードのみ抽出するようにします。
FROM
products T1
WHERE
NOT EXISTS (
SELECT
1
FROM
sales_by_product T2
WHERE
T1.product_code = T2.product_code
);
SELECT
しているproducts T1
とNOT EXISTS
内のSELECT
文のsales_by_product T2
をproduct_code
で結合することで、T1
、T2
双方に存在するsales_by_product
が抽出できます。ということは、T1
はNOT EXISTS
以外に絞り込み条件を持たないので、すべてのT1
のレコード(すべての商品)について、それぞれのレコードに対応するT2
(商品別売上実績)を抽出してみて、それが存在しなかったら(NOT EXISTS
)、SELECT
されるということになります。
NOT EXISTS
内のSELECT
している列が1となっていますが、これは行が抽出できていること自体にしか意味がないので、無駄なデータを抽出しないために適当な1
という値を指定しているだけです。'x'
とか3
とかでも問題ありませんし、データ量のことを気にしないのであれば*
でも構いません。
INSERTする前にSELECTだけを実行してみる
実際にINSERT
をする前にINSERT
部分以外のSELECT
文単独で実行結果を確認しておきます。実務においてもまずはINSERT
したい形と同じかをSELECT
だけで確認し、それが確認できたらINSERT
するのが安全です。
SELECT
T1.product_code,
0 total_sales_amount
FROM
products T1
WHERE
NOT EXISTS (
SELECT
1
FROM
sales_by_product T2
WHERE
T1.product_code = T2.product_code
);
まずは、INSERT
前の商品別売上実績テーブルを確認してみます。
SELECT * FROM sales_by_product;
=>
S001,50
S003,250
S004,350
S006,450
商品コードS002
、S005
が欠損していることがわかります。これが売上合計金額0
で抽出できればINSERT
したい内容と一致します。
SELECT
T1.product_code,
0 total_sales_amount
FROM
products T1
WHERE
NOT EXISTS (
SELECT
1
FROM
sales_by_product T2
WHERE
T1.product_code = T2.product_code
);
=>
S002,0
S005,0
問題なさそうです。
では実際にINSERT
までやって、再度全件抽出してみます。
INSERT INTO sales_by_product
SELECT
T1.product_code,
0 total_sales_amount
FROM
products T1
WHERE
NOT EXISTS (
SELECT
1
FROM
sales_by_product T2
WHERE
T1.product_code = T2.product_code
);
SELECT * FROM sales_by_product;
=>
S001,50
S002,0
S003,250
S004,350
S005,0
S006,450
商品コードS002
とS005
が売上合計金額0
で登録されていることが確認できました。
トランザクション機能を使って確認後にコミットする
今回は自動トランザクションでINSERT
したら自動コミットされるようにしています。
実務では手動トランザクションで明示的にコミットするまではDBにコミットされないようにしておくことで、INSERT
後の確認でOKになるまでは作業のやり直しができるようにしておくことが重要です。
まとめ
INSERT SELECT
を使ってあるテーブルから直接INSERT
する方法について確認しました。バッチなどでのバルクインサートやテーブルのバックアップ・リストアなどINSERT SELECT
を行うシーンは意外とあります。
INSERT
したい結果を抽出できるSELECT
文を書いて、その前にINSERT INTO
テーブル名を付けるということを覚えておけば、それほど悩むこともないと思います。細かい点を抜きにすればUPDATE SELECT
とは異なり、RDBMSによって構文が変わるということもないので、どれかのRDBMSでやり方を覚えておけば他のRDBMSでもほぼそのまま適用できます。