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

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

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

1 使用するテーブルの準備
- 1.1 テーブルの定義
- 1.2 テーブルの作成とデータの登録
2 SELECT INTOでテーブルを複製
3 複数のテーブルを結合して別のテーブルを作成
4 集計結果のテーブルを作成する
- 4.1 集計結果のテーブルを作成するサンプルテーブル
- 4.2 集計結果のテーブルを作成する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 [顧客];

実行結果

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

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

上記の顧客テーブルの複製を作成する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ステートメントでは、複製の作成元のテーブルに一部の列のみをコピーすることや、特定の条件に一致するレコードをもとにコピーすることも可能です。

一部の列のみをコピー

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

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

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

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

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

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

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

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

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

WHERE [顧客ID] <= 3;

顧客コピー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 TABLE [注文]
(
    [注文ID] int PRIMARY KEY,
    [顧客ID] int,
    [金額] int
);

CREATE TABLE [注文]

(

[注文ID] int PRIMARY KEY,

[顧客ID] int,

[金額] int

);

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を実行すると、以下のようなテーブルが作成されます。

テーブルのデザイン

テーブルのデータ