SQLのInsert文をNumbersで楽に作る（Exelでも可）

はじめに＿作成背景

仕事が落ち着いてかなり久しぶりの投稿だぁー（今後無理しない程度に投稿していきたい）
現場で本番データベースから情報を抽出して、検証用データベースにその情報をSQLを使って登録してこい！と言われた時に今ままでほとんどInsert文を書いたことがなかったため、苦労しました。
その中でも、
Insert文の「Values」に記載する、

Insert INTO テーブル名　（エンティティ名）　Values
（Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9,Value10,・・・）, （Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9,Value10,・・・）, （Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9,Value10,・・・）,
の「ValueN」のスマートなまとめ方が分からず仕事中にとてもひとりで恥ずかしい思いをしました。そこで、エクセルで事前に加工したものをまとめれば正確に効率よく記載することができることを学んだので、その学習成果として記録。細かい関数の詳細についてはあまり触れず、利便性を重視し直感的な説明を行います。
別の方法などもあるかと思いますが、自分が調べてみて感動したのでこの方法を伝えるべく共有します！

やりたいこと

以下の表のような、本番データベースから抽出したデータをまとめたエクセルにあるテーブルデータを検証用データベースにInsert文で正確に効率よく入力する

私が行なったInsert作成時のおっそいやり方

ただ、調べている時間がなかったのでとにかくInsert文の形にするために何の考えずに下のようにやりました。

【実際の作成方法】
エクセルをサクラエディタにValueNの範囲指定したものをコピペして貼り付けた後、varchar型かどうかを項目を一つずつ調べて「'」を一つ一つ付けた。また、上記のように貼り付けると、スペースが含まれるためそのスペースを削除する作業も行なったりカンマもつける作業も一つ一つ行なった。

上記のやり方の場合、varchar型などは値に「'」で囲ってあげるなどの必要があるため、繰り返し作業が多く、ケアレスミスが生じやすいという問題が発生します。

一つ一つ項目をコピペする方法もあるのですが、データが多くなればなるほど作業量が増えてめんどくさい...

解決策

結論、主に以下の関数を利用すると正確に効率よくInsert文を記載することができました。

CONCAT
TEXTJOIN
TYPE（Numbersだとなかったので代わりにISNUMBER関数を使いました）

（自分はMacを利用しているのでNumbersで説明しますが、Exelでも同様のことが可能です）

CONCAT

指定したセル同士や、指定したセルと任意の文字などを結合することができる関数

セルB5の入力内容・・・住所：
セルB６の入力内容・・・東京都
セルB７の入力内容・・・２３区
の場合、

例1　セルB8に「=CONCAT(B5,B6,B7)」とすると、結果としてセルB8に「住所：東京都２３区」と表示される
例２　セルB9に「=CONCAT("住所は",B6,B7,"内にあります")
」とすると、結果としてセルB9に「住所は東京都２３区内にあります」と表示される

TEXTJOIN

列指定したセルに、任意の文字をセル間に入れて結合することができる関数

セルB5の入力内容・・・住所
セルB６の入力内容・・・東京都
セルB７の入力内容・・・２３区
セルB８の入力内容・・・（空白）
の場合、

例１　セルB9に「=TEXTJOIN("_",TRUE,B5：B８)」とすると、結果としてセルB9に「住所_東京都_２３区」と表示される
※TEXTJOINの２つ目の引数である「TRUE」について、空白セルを考慮する場合、TRUEを設定し、空白セルを省略する場合、FALSEを設定する（データベース登録時はセルの内容が万が一空白の場合に省略されないようにTRUEを使って登録内容を作成していきます）
例２　セルB10に「=TEXTJOIN（"",FALSE,B5:B8）」とすると、結果としてセルB10に「住所東京都２３区」と表示される

TYPE

指定したセルの型を判定できる関数
ISNUMBER関数は指定したセルの方が数値型であるかを判定できる関数

セルB5の入力内容・・・住所
セルB６の入力内容・・・東京都
セルB７の入力内容・・・２３
の場合、

例１　セルB8に「=ISNUMBER(B5)」と入力すると結果としてセルB8に「FALSE」が表示される
例２　セルB10に「=ISNUMBER(B７)」と入力すると結果としてセルB10に「TRUE」が表示される

上記３種類と、IF関数を使って処理を記載する

利用方法（具体的な処理の記載）

1. ISNUMBERとIF関数を利用して、セルが数値型と数値型以外で条件分けを行う
   1-1 セル内が数値型の場合、そのまま出力して処理を終了する
   1-2 セル内が数値型以外の場合、２の処理を行う

２.　CONCAT関数を利用して、セル内の文字の両端に「'」を結合する

３. 全てのセルに対して1.と2.の数式をコピペする（画像の緑枠を右・下にドラッグする）

上記より
・数値型の場合は、そのまま表示
・数値型以外の場合は、文字の両端に「'」が結合された状態で表示

された状態になります。

４. ただし、この状態だと、「NULL」の値も両端に「'」が付いてしまうため、以下のように処理を追加しましょう。

そうすると、以下のような表示になります。

・数値型およびNULLの場合は、そのまま表示
・数値型およびNULL以外の場合は、文字の両端に「'」が結合された状態で表示

　

作成後、Insert文を作っていきますが、実際の現場では誤作動防止のため、SQL入力欄に直接打つのではなく、サクラエディタなどのエディタにInsert文を作成したものをコピペして実行する方法を推奨します。

この状態で、例えば、エクセルでコピーし、サクラエディタにペーストを行なった場合、謎のスペースができてしまい、そのままInsert文を実行すると、パーティションエラー（スペースがあることで起きるエラー）が起こる原因になりかねません。また、「,(カンマ)」も一つ一つつけなければならないので面倒です。
そのため、TEXTJOIN関数を利用します。

５. TEXTJOIN関数を利用して、指定列のセル間に「,」を入れながら結合する

以下のように関数を記載すると、

このように表示されますので、あとは他の行にも同じことを行うと、

取りたいものが取れました！（オレンジ色）

あとはInsert文に入れてSQLを実行すればデータ登録できるはずです。
私の場合は、データベースのアクセス権限の設定がミスっていたみたいで、エラーになって登録できませんでした...トホホ

作成日 2023/12/01