SELECT INTOで他のテーブルからデータを複製またはテーブルを作成する [SQLServer]

データベースSQLServer SELECT INTO テーブル複製

プログラムの登録、更新、削除のテストをしていると、操作対象のテーブルのデータを一旦退避させたり、テスト用に本番データベースのテーブルをコピーしたりする時があります。

そこで今回は、SQLServerで既存のテーブルのデータをもとに、SELECT INTOステートメントで新しいテーブルを複製、または作成する方法を紹介します。

使用するテーブルの準備

SELECT INTOステートメントでテーブルを複製するサンプルのSQLを実行するために簡単なテーブルを作成します。
作成するテーブルは顧客IDと氏名を持つ顧客テーブルと、顧客テーブルの1件のレコードに対して複数件のレコードが紐づく顧客電話番号テーブルです。

テーブルの定義

顧客テーブルと顧客電話番号テーブルの定義は次のようにします。

顧客テーブル
列名 データ型 PK
顧客ID INT
顧客名 NVARCHAR(50)
顧客電話番号テーブル
列名 データ型 PK
顧客ID INT
電話番号 NVARCHAR(20)

テーブルの作成とデータの登録

テーブルを作成するCREATE文のSQLと、データを登録するINSERT文のSQLを記載します。

CREATE文

INSERT文

上記の顧客テーブル、顧客電話番号テーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括して挿入する方法については、以下の記事を参照してください。

1回のINSERT(インサートSQL)で複数行のレコードを一括挿入(追加)する
SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

SELECT INTOでテーブルを複製

SELECT INTOステートメントは、SELECTステートメントとINSERT INTOステートメントを組み合わせたような構文をしています。

SELECTステートメントでテーブルからデータを取得する場合は、以下のようなSQLを実行します。

INSERT INTOステートメントでテーブルにデータを挿入する場合は、以下のようなSQLを実行します。

INSERT INTOステートメントでテーブルからテーブルへデータを挿入する場合は、以下のようなSQLを実行します。

SELECT INTOステートメントは次のような構文で使用します。

INSERT INTOステートメントの「INSERT」部分を「SELECT *」に変更、またはSELECTステートメントの「SELECT * FROM」の「*」と「FROM」の間に「INTO [複製として作成するテーブル]」を挟む感じです。

SELECT INTOステートメントの構文は大きく「SELECT句」「INTO句」「FROM句」の3つの要素で構成されています。
「SELECT句」が「何を?」⇒ 列
「INTO句」が「どこに?」⇒ 作成するテーブル
「FROM句」が「どこから?」⇒ 作成のもとになるテーブル
をそれぞれ指定します。

顧客テーブルを例に、SELECT INTOステートメントで複製(コピー)テーブルを作成すると次のようになります。

実行結果

顧客テーブルの複製テーブルをSELECT INTOで作成

作成されたテーブルの定義は以下のようになります。

顧客コピー1テーブルの定義

顧客コピー1テーブルの定義

SELECT INTOで作成するテーブルの列の制約について

SELECT INTOステートメントで複製として作成したテーブルには、複製の作成元となるテーブルのプライマリキーまではコピーされません。

上記の顧客テーブルの複製を作成するSELECT INTOステートメントでプライマリキーを設定するのであれば、ALTER TABLEステートメントを使用して以下のようにします。

顧客コピー1テーブルの定義

プライマリキーを作成した場合の顧客コピー1テーブルの定義

SELECT INTOステートメントで作成したテーブルには、プライマリキーの他にも、インデックス、制約、トリガーなどはコピーされませんので、テーブルを作成した後に必要に応じて追加で設定してください。

テーブルの一部の列や条件に一致するレコードのみ抜き出して複製する

SELECT INTOステートメントでは、複製の作成元のテーブルに一部の列のみをコピーすることや、特定の条件に一致するレコードをもとにコピーすることも可能です。

一部の列のみをコピー

次のSQLは顧客テーブルの顧客IDのみを顧客コピー2というテーブル名で複製しています。

顧客コピー2テーブルのデータ

顧客テーブルの顧客IDのみ複製したテーブルのデータ
上記の例では、顧客テーブルから「顧客ID」列のみ抜き出してテーブルを複製しています。

特定の条件に一致するレコードのみコピー

次のSQLは顧客テーブルの顧客IDが3以下のレコードのみを顧客コピー3というテーブル名で複製しています。

顧客コピー3テーブルのデータ

顧客テーブルの顧客IDが3以下のレコードのみ複製したテーブルのデータ
上記の例では、顧客テーブルから「顧客ID」が特定の条件に一致するレコードのみ(ここでは顧客IDが3以下のレコードのみ)を抽出した結果でテーブルを複製しています。

複数のテーブルを結合して別のテーブルを作成

SELECT INTOステートメントでは、単一のテーブルのデータを複製するだけではなく、複数のテーブルのデータをつなぎ合わせて1つのテーブルを作成することもできます。

以下に顧客テーブルと顧客電話番号テーブルを結合して、両テーブルにある列を抜き出して新しいテーブルの「顧客詳細」テーブルを作成する例を示します。

上記のSQLを実行すると、以下のようなテーブルが作成されます。

テーブルのデザイン

SELECT INTO ステートメントで作成した複数テーブルの結合結果デザイン

テーブルのデータ

SELECT INTO ステートメントで作成した複数テーブルの結合結果データ

集計結果のテーブルを作成する

SELECT INTOステートメントは、SELECTステートメントで指定するFROM句以下に指定した条件の実行結果をもとにテーブルを作成しますので、グループ化(GROUP BY)した集計結果のテーブルを作成することもできます。

集計結果のテーブルを作成するサンプルテーブル

集計結果のテーブルを作成するためにサンプルテーブルを作成します。
テーブルを作成するCREATE文とINSERT文は以下のようになります。

CREATE文

INSERT文

集計結果のテーブルを作成するSELECT INTO

集計結果のテーブルを作成するSELECT INTOステートメントを以下に示します。
例として売上テーブルのデータを顧客ごと(顧客IDでグループ化した結果)の顧客の件数と金額の合計を取得してテーブルを作成します。

上記のSQLを実行すると、以下のようなテーブルが作成されます。

テーブルのデザイン

SELECT INTO ステートメントで作成した複数テーブルの集計結果デザイン

テーブルのデータ

SELECT INTO ステートメントで作成した複数テーブルの集計結果データ

SELECT句で関数や式などを使って列を取得する場合はASキーワードを使用して列の別名をつける必要があります。
上記の例では、件数を取得するCOUNT関数を使用している「COUNT([顧客ID])」には「[顧客数]」という列名を付け、合計を取得するSUM関数を使用している「SUM([金額]) 」には「[金額]」という列名を付けています。

作成テーブルを一時テーブルとして作成する

SELECT INTOで作成するテーブルの名前に「#」を付けることでローカルの一時テーブルになります。

「#」を2つにして「##」とすることでグローバルの一時テーブルになります。

別のデータベースにテーブルを作成する

SELECT INTOステートメントを使用したテーブルの作成は、同じインスタンス内であれば別のデータベースに対して行うことも可能です。

別のデータベースに作成する場合は、「INTO」の後に指定するテーブルの部分で

[テーブルを作成データベース名].[スキーマ名].[作成するテーブル名]

の形式でテーブル名を指定します。

SELECT INTOで作成するテーブルの利用

データ量が非常に多いテーブルに対して、複雑で大きなSQLクエリを実行すると、データを取得するのに時間がかかってしまう場合があります。

SELECT INTOステートメントを使用して別のテーブルを一旦作成してから、そのテーブルに対してデータを取得するSQLクエリを実行することで、データ取得のパフォーマンスが向上する場合があります。

テーブル名に「#」または「##」を付けた一時テーブルを利用すれば、データベースに実テーブルを作成することなくデータを取得することができます。

おすすめの書籍