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

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

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

1 使用するテーブルの準備
- 1.1 テーブルの定義
- 1.2 テーブルの作成とデータの登録
2 SELECT INTOでテーブルを複製
3 SELECT INTOで作成するテーブルの列の制約について
4 テーブルの一部の列や条件に一致するレコードのみ抜き出して複製する
5 複数のテーブルを結合して別のテーブルを作成
6 集計結果のテーブルを作成する
- 6.1 集計結果のテーブルを作成するサンプルテーブル
- 6.2 集計結果のテーブルを作成するSELECT INTO
7 作成テーブルを一時テーブルとして作成する
8 別のデータベースにテーブルを作成する
9 SELECT INTOで作成するテーブルの利用

使用するテーブルの準備

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

テーブルの定義

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

顧客テーブル

列名	データ型	PK
顧客ID	INT	○
顧客名	NVARCHAR(50)

顧客電話番号テーブル

列名	データ型	PK
顧客ID	INT	○
電話番号	NVARCHAR(20)	○

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

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

CREATE文

-- 顧客テーブルの作成
CREATE TABLE [顧客]
(
	[顧客ID] [int] NOT NULL,
	[顧客名] [nvarchar](50) NOT NULL,
  CONSTRAINT [PK_顧客] PRIMARY KEY CLUSTERED 
  (
	[顧客ID] ASC
  )
);
GO

-- 顧客電話番号テーブルの作成
CREATE TABLE [顧客電話番号]
(
	[顧客ID] [int] NOT NULL,
	[電話番号] [nvarchar](20) NOT NULL,
  CONSTRAINT [PK_顧客電話番号] PRIMARY KEY CLUSTERED 
  (
	[顧客ID] ASC,
    [電話番号] ASC
  )
);
GO

-- 顧客テーブルの作成

CREATE TABLE [顧客]

(

[顧客ID] [int] NOT NULL,

[顧客名] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_顧客] PRIMARY KEY CLUSTERED

(

[顧客ID] ASC

)

);

-- 顧客電話番号テーブルの作成

CREATE TABLE [顧客電話番号]

(

[顧客ID] [int] NOT NULL,

[電話番号] [nvarchar](20) NOT NULL,

CONSTRAINT [PK_顧客電話番号] PRIMARY KEY CLUSTERED

(

[顧客ID] ASC,

[電話番号] ASC

)

);

INSERT文

-- 顧客テーブルのデータを挿入
INSERT INTO [顧客]
    ([顧客ID], [顧客名])
VALUES
    (1, '顧客名1'),
    (2, '顧客名2'),
    (3, '顧客名3'),
    (4, '顧客名4'),
    (5, '顧客名5');

-- 顧客電話番号テーブルのデータを挿入
INSERT INTO [顧客電話番号]
    ([顧客ID], [電話番号])
VALUES
    (1, '000-000-0000'),
    (2, '111-111-1111'),
    (3, '222-222-2222'),
    (4, '333-333-3333'),
    (5, '444-444-4444');

-- 顧客テーブルのデータを挿入

INSERT INTO [顧客]

([顧客ID], [顧客名])

VALUES

(1, '顧客名1'),

(2, '顧客名2'),

(3, '顧客名3'),

(4, '顧客名4'),

(5, '顧客名5');

-- 顧客電話番号テーブルのデータを挿入

INSERT INTO [顧客電話番号]

([顧客ID], [電話番号])

VALUES

(1, '000-000-0000'),

(2, '111-111-1111'),

(3, '222-222-2222'),

(4, '333-333-3333'),

(5, '444-444-4444');

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

SELECT INTOでテーブルを複製

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

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

-- SELECTステートメントで顧客テーブルのデータを取得
SELECT * FROM [顧客] ORDER BY [顧客ID];

1 2	-- SELECTステートメントで顧客テーブルのデータを取得 SELECT * FROM [顧客] ORDER BY [顧客ID];

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

-- INSERT INTOステートメントで顧客電話番号テーブルのデータを挿入
INSERT INTO [顧客電話番号]
(
    [顧客ID],
    [電話番号]
)
VALUES
(
    4,
    '012-345-6789'
)

-- INSERT INTOステートメントで顧客電話番号テーブルのデータを挿入

INSERT INTO [顧客電話番号]

(

[顧客ID],

[電話番号]

)

VALUES

(

'012-345-6789'

)

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

-- 顧客電話番号テーブルに初期レコードを挿入
INSERT INTO [顧客電話番号]
(
    [顧客ID],
    [電話番号]
)
SELECT
    [顧客ID],
    ''
FROM
    [顧客];

-- または
INSERT INTO [顧客電話番号] SELECT * FROM [顧客];

-- 顧客電話番号テーブルに初期レコードを挿入

INSERT INTO [顧客電話番号]

(

[顧客ID],

[電話番号]

)

SELECT

[顧客ID],

FROM

[顧客];

-- または

INSERT INTO [顧客電話番号] SELECT * FROM [顧客];

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

SELECT * INTO [複製として作成するテーブルの名前] FROM [複製の作成元のテーブル];

1	SELECT * INTO [複製として作成するテーブルの名前] FROM [複製の作成元のテーブル];

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

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

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

-- 顧客テーブルの複製テーブルを作成する
SELECT * INTO [顧客コピー1] FROM [顧客];

1 2	-- 顧客テーブルの複製テーブルを作成する SELECT * INTO [顧客コピー1] FROM [顧客];

実行結果

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

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

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

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

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

-- 顧客テーブルの複製テーブルを作成する
SELECT * INTO [顧客コピー1] FROM [顧客];

-- 複製したテーブルのプライマリキーを追加する
ALTER TABLE [顧客コピー1] ADD CONSTRAINT
    [PK_顧客コピー1]
PRIMARY KEY ([顧客ID]);

-- 顧客テーブルの複製テーブルを作成する

SELECT * INTO [顧客コピー1] FROM [顧客];

-- 複製したテーブルのプライマリキーを追加する

ALTER TABLE [顧客コピー1] ADD CONSTRAINT

[PK_顧客コピー1]

PRIMARY KEY ([顧客ID]);

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

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

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

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

一部の列のみをコピー

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

-- 顧客テーブルの顧客IDのみ複製する
SELECT [顧客ID] INTO [顧客コピー2] FROM [顧客];

1 2	-- 顧客テーブルの顧客IDのみ複製する SELECT [顧客ID] INTO [顧客コピー2] FROM [顧客];

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

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

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

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

-- 顧客テーブルの顧客IDが3以下のレコードのみ複製する
SELECT * INTO [顧客コピー3] FROM [顧客]
WHERE [顧客ID] <= 3;

-- 顧客テーブルの顧客IDが3以下のレコードのみ複製する

SELECT * INTO [顧客コピー3] FROM [顧客]

WHERE [顧客ID] <= 3;

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

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

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

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

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

-- 顧客テーブルと顧客電話番号テーブルを結合した結果のテーブルを作成する
SELECT
    [顧客].[顧客ID],
    [顧客].[顧客名],
    [顧客電話番号].[電話番号]
INTO
    [顧客詳細]
FROM
    [顧客]
LEFT OUTER JOIN
    [顧客電話番号]
ON
    [顧客].[顧客ID] = [顧客電話番号].[顧客ID]

-- 顧客テーブルと顧客電話番号テーブルを結合した結果のテーブルを作成する

SELECT

[顧客].[顧客ID],

[顧客].[顧客名],

[顧客電話番号].[電話番号]

INTO

[顧客詳細]

FROM

[顧客]

LEFT OUTER JOIN

[顧客電話番号]

[顧客].[顧客ID] = [顧客電話番号].[顧客ID]

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

テーブルのデザイン

テーブルのデータ

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

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

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

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

CREATE文

CREATE TABLE [注文]
(
    [注文ID] int PRIMARY KEY,
    [顧客ID] int,
    [金額] int
);

CREATE TABLE [注文]

(

[注文ID] int PRIMARY KEY,

[顧客ID] int,

[金額] int

);

INSERT文

INSERT INTO
    [注文]
VALUES
    (1, 2, 1800),
    (2, 5, 1500),
    (3, 1, 3000),
    (4, 3, 1250),
    (5, 5, 2600),
    (6, 4, 1840),
    (7, 3, 1600),
    (8, 2, 2700),
    (9, 5, 3250),
    (10, 4, 2900)

INSERT INTO

[注文]

VALUES

(1, 2, 1800),

(2, 5, 1500),

(3, 1, 3000),

(4, 3, 1250),

(5, 5, 2600),

(6, 4, 1840),

(7, 3, 1600),

(8, 2, 2700),

(9, 5, 3250),

(10, 4, 2900)

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

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

SELECT
    [顧客ID],
    COUNT([顧客ID]) AS [顧客数],
    SUM([金額]) AS [金額]
INTO
    [顧客別売上]
FROM
    [注文]
GROUP BY
    [顧客ID]

SELECT

[顧客ID],

COUNT([顧客ID]) AS [顧客数],

SUM([金額]) AS [金額]

INTO

[顧客別売上]

FROM

[注文]

GROUP BY

[顧客ID]

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

テーブルのデザイン

テーブルのデータ

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

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

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

SELECT
    [顧客ID],
    COUNT([顧客ID]) AS [顧客数],
    SUM([金額]) AS [金額]
INTO
    [#顧客別売上]
FROM
    [注文]
GROUP BY
    [顧客ID]

SELECT

[顧客ID],

COUNT([顧客ID]) AS [顧客数],

SUM([金額]) AS [金額]

INTO

[#顧客別売上]

FROM

[注文]

GROUP BY

[顧客ID]

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

SELECT
    [顧客ID],
    COUNT([顧客ID]) AS [顧客数],
    SUM([金額]) AS [金額]
INTO
    [##顧客別売上]
FROM
    [注文]
GROUP BY
    [顧客ID]

SELECT

[顧客ID],

COUNT([顧客ID]) AS [顧客数],

SUM([金額]) AS [金額]

INTO

[##顧客別売上]

FROM

[注文]

GROUP BY

[顧客ID]

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

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

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

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

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

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

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

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

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

おすすめの書籍

リンク