他のテーブルから SELECT INTO でテーブルを作成する
他のテーブルから SELECT INTO でテーブルを作成する
「 CREATE 文でテーブルを作成する 」 では、 CREATE 文を使って、クエリーを実行してテーブルを作成しました。
今回は、他のテーブルなどから取得した SELECT の結果を元に、SELECT INTO を使って新しいテーブルを作ってみましょう。
こちら のスクリプトを実行して、テーブルを再生成しておいてください。 全てのテーブルは使いませんが、これらのテーブルが存在している前提で進めます。
SELECT INTO ステートメントの使い方
SELECT INTO を使って [テーブル1] の [カラム1],[カラム2],[カラム3] を持ち、[条件1] にマッチした行のみが存在する、新しい [テーブル2] を作るには、次のようにします。
SELECT [カラム1], [カラム2], [カラム3] INTO [テーブル2] FROM [テーブル1] WHERE [条件1]
上の例では、[テーブル1] のみからデータを取得していますが、SELECT 文のデータ元は、複数のテーブルを結合しても、ビューでも、集計した結果などでも大丈夫です。
SELECT 句で関数や式などを使った場合は AS でカラム名をつける必要があります。
また重複するカラム名があるとエラーになるので、その時も AS で重複がなくなるようにカラム名を指定してください。
INTO で指定する新しくできるテーブル名は、名前の前に # をつけるとローカル一時テーブルになり、## をつけるとグローバル一時テーブルになり、つけないと普通のテーブルになります。
SELECT INTO で生成されたテーブルには、元のテーブルについていた、キー、インデックス、制約、トリガーなどはコピーされません。
また、SELECT INTO を使って、同じインスタンス内の別のデータベースにテーブルを作成することもできます。
その時は INTO の後に、[テーブルを作りたいデータベース名].[スキーマ名].[新しいテーブル名] のように指定します。
データ量が多いテーブルに対して、複雑なひとつの大きなクエリーを書くと、データの取得にすごく時間がかかることがあります。
そういう時は、まず対象データを SELECT INTO などで一時テーブルに取得し、その一時テーブルに対してクエリーを書くとパフォーマンスが向上することがよくあります。
SELECT INTO でテーブルを作成する
それでは、実際に SELECT INTO 文を実行して、テーブルを作ってみましょう。
次のような Test テーブルと TestResult テーブルがあります。
Test テーブルと TestResult テーブルから、テストの英語名と、最高点、最低点、平均点、学生数を集計した結果を持つ、TestResultSummary2 という名前の新しいテーブルを作ってみましょう。
SELECT T.TestID,
T.TestNameEn,
MIN(Score) AS MIN_Score,
MAX(Score) AS MAX_Score,
AVG(CAST(Score AS DECIMAL)) AS AVG_Score,
COUNT(StudentID) AS COUNT_Student
INTO TestResultSummary2
FROM TestResult AS TR
INNER JOIN Test AS T
ON TR.TestID = T.TestID
GROUP BY T.TestID,
T.TestNameEn;
[ 実行結果 ]
クエリー結果のレコードを持つ、TestResultSummary2 テーブルが生成されました。
オブジェクトエクスプローラで見てもわかるように、キーなどはコピーされていませんし、既存のテーブルのデータから生成できる情報は正規化を崩したり、片方のテーブルのデータを更新してもう一方更新を忘れるなど、データの整合性がおかしくなってしまうことがあるので、この方法でできたテーブルを永久的にそのまま使うのはおすすめしません。
私は SELECT INTO を使う際は、名前の前に # をつけて、ローカル一時テーブルとして使うことが多いです。 一時テーブルについては、別ページでご紹介します。
次は、ALTER TABLE でテーブルのカラムを追加・変更・削除してみましょう。