データの挿入

テーブルの作成後、INSERT ステートメントまたはその他のステートメントを使用して、テーブルにレコードを挿入できます。本記事では、関連するステートメントの使い方と例を紹介します。

データ挿入の準備

データを挿入する前に、以下の項目を確認してください：

• データベースのOracleテナントに接続されていることを確認してください。データベース接続操作については、接続方法の概要を参照してください。

  説明

  現在ログインしているテナントが属するテナントモードは、sys テナントで oceanbase.DBA_OB_TENANTS ビューをクエリすることで確認できます。

• 操作対象のテーブルの INSERT 権限を持っていることを確認してください。現在のユーザー権限を確認する操作の詳細については、ユーザー権限の確認を参照してください。この権限を持っていない場合は、管理者に連絡して権限を付与してもらってください。ユーザー権限に関する操作については、直接権限付与を参照してください。

INSERT INTOステートメントを使用したデータの挿入

INSERT ステートメントを使用し、下記の提案を参考にしてテーブルにデータを挿入してください。

INSERT INTO ステートメントの構文は次のとおりです：

INSERT INTO table_name [(list_of_columns)] VALUES (list_of_values);

データの挿入に関する推奨事項

• データを挿入する前に、列のタイプ、有効な値、NULLを許容するかどうかなど、テーブルのすべての列情報について確認することを推奨します。

  列情報は DESC ステートメントで確認できます。

  obclient [SYS]> DESC ordr;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER       | YES  | NULL | NULL    | NULL  |
  | C2    | VARCHAR2(50) | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  2 rows in set
  

  • 列属性が NOT NULL の場合

    • 列属性にデフォルト値が設定されている場合、挿入時にその列の値を指定しなくても、システムがその列にデフォルト値を挿入します。

    • 列属性にデフォルト値が設定されていない場合、挿入時にはその列の値を指定する必要があります。

  • 列属性が NULL の場合、挿入時にその列の値を指定しなくても、システムがその列に NULL 値を挿入します。

• データを挿入する前に、テーブルの列の制約定義を確認し、データ挿入時のエラーを避けることを推奨します。

  NOT NULL、PRIMARY KEY 制約、UNIQUE 制約はすべて DESC ステートメントで確認できます。FOREIGN KEY、CHECK 制約は、ALL_CONSTRAINTS、DBA_CONSTRAINTS または USER_CONSTRAINTS ビューをクエリすることで確認できます。

単一行データの挿入

INSERT ステートメントを使用することで、単一行のデータを挿入できます。複数のレコードを挿入する必要がある場合は、複数の単一行挿入ステートメントを実行することで対応できます。一括挿入が必要な場合は、複数行データの一括挿入 を参照して操作することができます。

データを挿入するテーブルの想定情報は以下のとおりです：

obclient [SYS]> CREATE TABLE t_insert(
    id number NOT NULL PRIMARY KEY,
    name varchar(10) NOT NULL, 
    value number,
    gmt_create DATE NOT NULL DEFAULT sysdate
 );
Query OK, 0 rows affected 

この中で、テーブルの id 列と name 列は空欄にすることができず、id 列は主キーであるため、一意性制約の要件を満たす必要があり、重複する値を持つことはできません。gmt_create 列にはデフォルト値が設定されています。

例1：複数の単一行挿入ステートメントを使用して、複数行のデータを挿入します。

gmt_create 列にはデフォルト値が指定されているため、データ挿入時にデフォルト値を指定する必要はありません。

obclient [SYS]> INSERT INTO t_insert(id, name, value) 
VALUES (1,'CN',10001);
Query OK, 2 rows affected

obclient [SYS]> INSERT INTO t_insert(id, name, value) 
VALUES(2,'US', 10002);
Query OK, 2 rows affected

注意：gmt_create 列にデフォルト値が指定されていない場合、データ挿入時に値を指定し、以下のステートメントを使用する必要があります。

obclient [SYS]> INSERT INTO t_insert(id, name, value, gmt_create)
 VALUES (3,'EN', 10003, sysdate);
Query OK, 1 row affected 

複数行データの一括挿入

データ挿入時に、複数のレコードを挿入する必要がある場合は、1つの INSERT ステートメントに複数の VALUES を含めて、一括挿入を行うこともできます。単一の複数行挿入ステートメントは、複数の単一行挿入ステートメントよりも高速です。

例1の操作は、以下のステートメントでも実行できます。

例2：複数行データを一括挿入します。

obclient [SYS]> INSERT INTO t_insert(id, name, value) 
  VALUES (1,'CN',10001),(2,'US', 10002);
Query OK, 2 rows affected

さらに、テーブルデータのバックアップが必要な場合、またはテーブルのすべてのレコードを別のテーブルにコピーする必要がある場合、INSERT INTO ... SELECT ... FROM クエリを使用して、INSERT の values 句として一括挿入を実行できます。

例3：テーブル t_insert の全データを t_insert_bak テーブルにバックアップします。

obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 31-OCT-22  |
|  2 | US   | 10002 | 31-OCT-22  |
|  3 | EN   | 10003 | 31-OCT-22  |
+----+------+-------+------------+
3 rows in set

obclient [SYS]> CREATE TABLE t_insert_bak(
    id number NOT NULL PRIMARY KEY,
    name varchar(10) NOT NULL, 
    value number,
    gmt_create DATE NOT NULL DEFAULT sysdate
 );
Query OK, 0 rows affected 

obclient [SYS]> INSERT INTO t_insert_bak SELECT * FROM t_insert;
Query OK, 2 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient [SYS]> SELECT * FROM t_insert_bak;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 31-OCT-22  |
|  2 | US   | 10002 | 31-OCT-22  |
|  3 | EN   | 10003 | 31-OCT-22  |
+----+------+-------+------------+
3 rows in set

INSERT OVERWRITE SELECTステートメントを使用してデータを挿入する

INSERT OVERWRITE SELECT ステートメントは、クエリ結果でテーブル内の既存データを置き換え、クエリで取得したデータをターゲットテーブルに上書きすることができます。この操作では原子性が保証されており、実行中に例外が発生した場合、操作全体がロールバックされます。

このステートメントの構文は以下のとおりです：

INSERT [/*+PARALLEL(N)*/] OVERWRITE table_name [(list_of_columns)] [PARTITION (partition_name, ...)] select_stmt;

INSERT OVERWRITE SELECTの使用制限

テーブルレベルのINSERT OVERWRITE SELECTの使用制限

• このステートメントは、複数行トランザクションでは操作できません。そのため、操作をスムーズに実行するために、まず SET autocommit = on; コマンドを実行して自動トランザクションコミットモードを有効にする必要があります。
• 書き込み対象のテーブルにテーブルロックがかかります。同じテーブルに対してDDL操作を同時に実行することは許可されず、同時に実行されたDML操作はテーブルロックが解放されるまでタイムアウトするまで待機します。操作中にテーブルに対してクエリを実行することは可能です。
• このステートメントが操作するソースデータと、ターゲットテーブルで上書きされる列の数は、厳密に一致している必要があります。一致しない場合はエラーが発生します。
• このステートメントのデータ書き込み操作は、フル・ダイレクトロード方式を採用しているため、操作はフル・ダイレクトロード機能の制限を受けます。ダイレクトロードに関する情報は、INSERT INTO SELECTステートメントによるデータのダイレクトロード の 使用上の制限 セクションを参照してください。
• このステートメントは、ダイレクトロードのヒントを指定するとエラーが発生します。
• PDML（Parallel Data Manipulation Language、並列データ操作言語）フレームワークの制限により、PDMLでサポートされていないシナリオではデータのインポートができません。INSERT OVERWRITE SELECT は、「not supported」エラーを報告をします。並列DMLの詳細については、並列DMLを参照してください。

パーティションレベルのINSERT OVERWRITE SELECT

機能紹介

• ソーステーブルはパーティションテーブルでも非パーティションテーブルでも構いません。パーティショニングタイプに制限はありません。
• ターゲットテーブルは、パーティションまたはサブパーティションをサポートします。
• ターゲットテーブルはローカルインデックスとLOBをサポートしています。
• ターゲットテーブルのすべてまたは一部のパーティションを指定できます。ターゲットテーブルにサブパーティションがある場合は、パーティションとサブパーティションを個別または混合して指定できます。
• ソーステーブルからのデータがターゲットテーブルのパーティションルールに従って計算された後、指定されたターゲットテーブルのパーティションにヒットしない場合、パーティションが存在しないというエラーが発生します。
• 指定されたターゲットテーブルのパーティションにソーステーブルからのデータがヒットしない場合、そのパーティションのデータはクリアされます（空データで上書きされます）。
• OceanBaseデータベースV4.3.5 BP1バージョンから、パーティションレベルの insert overwrite 操作は、ターゲットテーブルの最後のレベルのパーティションがHashまたはKeyのパーティションであることをサポートします。

使用制限

• 指定されたパーティションは既に存在している必要があります。そうでない場合、エラーが発生します。
• フルダイレクトロードに基づいており、/*+ append */ ヒントを追加する必要はありません。そうしないと、使用時にエラーが発生します。
• このステートメントは、複数行トランザクションでは操作できません。そのため、操作をスムーズに実行するために、まず SET autocommit = on; コマンドを実行して自動トランザクションコミットモードを有効にする必要があります。
• 現在、ターゲットテーブルにidentity列があることはサポートされていません。
• 現在、ターゲットテーブルにグローバルインデックスまたは外部キーはサポートされていません。
• 現在、ターゲットテーブルとして外部テーブルはサポートされていません。

INSERT OVERWRITE SELECTの例

例1

1. 以下のSQLを実行して、自動トランザクションコミットモードを有効にします。

   SET autocommit = on;
   

2. テストテーブルを2つ作成します：source_tbl1 をデータソースとし、target_tbl1 をターゲットテーブルとします。

   CREATE TABLE source_tbl1 (col1 INT, col2 VARCHAR2(20), col3 INT);
   

   CREATE TABLE target_tbl1 (col1 INT, col2 VARCHAR2(20), col3 INT);
   

3. source_tbl1 テーブルにサンプルデータを追加します。

   INSERT INTO source_tbl1 VALUES (1, 'A1', 30),(2, 'B2', 25),(3, 'C3', 22);
   

4. target_tbl1 テーブルにサンプルデータを追加します。

   INSERT INTO target_tbl1 VALUES (4, 'D4', 35),(5, 'E5', 28);
   

5. target_tbl1 テーブルからデータをクリエします。

   SELECT * FROM target_tbl1;
   

   実行結果は次のとおりです：

   +------+------+------+
   | COL1 | COL2 | COL3 |
   +------+------+------+
   |    4 | D4   |   35 |
   |    5 | E5   |   28 |
   +------+------+------+
   2 rows in set
   

6. INSERT OVERWRITE SELECT ステートメントを使用して、col3 が25より大きいデータを source_tbl1 から選択し、それらのデータを target_tbl1 に挿入して、既存の内容を置き換えます。

   INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;
   

7. target_tbl1 テーブルの置き換え後のデータを確認します。

   SELECT * FROM target_tbl1;
   

   実行結果は次のとおりです：

   +------+------+------+
   | COL1 | COL2 | COL3 |
   +------+------+------+
   |    1 | A1   |   30 |
   +------+------+------+
   1 row in set
   

例2

パーティションレベルの INSERT OVERWRITE PARTITION の使用例:

1. 以下のSQLを実行して、自動トランザクションコミットモードを有効にします。

   obclient [SYS]> SET autocommit = on;
   

2. 以下のSQLを実行して、パーティションテーブル t1_ins_overwrt を作成します。

   obclient [SYS]> create table t1_ins_overwrt (c1 int primary key, c2 int) partition by range(c1)(
          partition p0 values less than(10),
          partition p1 values less than(20),
          partition p2 values less than(30),
          partition p3 values less than (MAXVALUE)
        );
   Query OK, 0 rows affected (0.223 sec)
   

3. 以下のSQLを実行してパーティションテーブル t2_ins_overwrt を作成します。

   obclient [SYS]> create table t2_ins_overwrt (c1 int primary key, c2 int) partition by range(c1)(
          partition p0 values less than(10),
          partition p1 values less than(20),
          partition p2 values less than(30),
          partition p3 values less than (MAXVALUE)
        );
   Query OK, 0 rows affected (0.092 sec)
   

4. それぞれのテーブルに数件のデータを挿入します。

   obclient [SYS]> insert into t1_ins_overwrt values (12, 20), (34, 40), (45, 50);
   Query OK, 3 rows affected (0.002 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   
   obclient [SYS]> insert into t2_ins_overwrt values (3, 60), (8, 80), (17, 170), (26, 260), (29, 290), (142, 1420);
   Query OK, 6 rows affected (0.003 sec)
   Records: 6  Duplicates: 0  Warnings: 0
   

5. それぞれのテーブルからパーティションP1のデータをクエリします。

   obclient [SYS]> select * from t1_ins_overwrt partition(p1);
   +------+------+
   | C1   | C2   |
   +------+------+
   |   12 |   20 |
   +------+------+
   1 row in set (0.006 sec)
   
   obclient [SYS]> select * from t2_ins_overwrt partition(p1);
   +------+------+
   | C1   | C2   |
   +------+------+
   |   17 |  170 |
   +------+------+
   1 row in set (0.007 sec)
   

6. 次のSQLを実行して、t1_ins_overwrt テーブルのパーティションp1のデータをt2_ins_overwrt テーブルの指定されたパーティションp1に挿入します。

   obclient [SYS]> insert overwrite t2_ins_overwrt partition(p1) select * from t1_ins_overwrt partition(p1);
   Query OK, 3 rows affected (1.360 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   

7. t2_ins_overwrt テーブルをクエリしたところ、パーティションp1のデータが上書きされていることが確認されました。

   obclient [SYS]> SELECT * FROM t2_ins_overwrt partition(p1);
   +------+------+
   | C1   | C2   |
   +------+------+
   |   12 |   20 |
   +------+------+
   1 row in set (0.020 sec)
   

例3

1. パーティションのテーブル tbl1 を作成します。

   obclient [SYS]> CREATE TABLE tbl1 (
    col1 INT,
    col2 INT
    )
    PARTITION BY RANGE (col1)
    SUBPARTITION BY HASH (col2)
    SUBPARTITIONS 3
    (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN (20)
    );
    Query OK, 0 rows affected (0.093 sec)
   

2. tbl1 テーブルに2件のデータを挿入します。

   obclient [SYS]>  INSERT INTO tbl1 (col1, col2) VALUES (2, 3);
                    INSERT INTO tbl1 (col1, col2) VALUES (15, 2);
   

3. テーブル tbl1 のパーティション p0、p1 にデータが挿入されているかをクエリします。

   obclient [SYS]> SELECT * FROM tbl1 PARTITION (p0);
   

   クエリ結果は以下のとおりです：

   +------+------+
   | col1 | col2 |
   +------+------+
   |    2 |    3 |
   +------+------+
   

   obclient [SYS]> SELECT * FROM tbl1 PARTITION (p1);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | col1 | col2 |
    +------+------+
    |   15 |    2 |
    +------+------+
   

4. パーティションのテーブル tbl2 を作成します。

   obclient [SYS]> CREATE TABLE tbl2 (
    col1 INT,
    col2 INT
    )
    PARTITION BY RANGE (col1)
    SUBPARTITION BY HASH (col2)
    SUBPARTITIONS 3
    (
        PARTITION p0 VALUES LESS THAN (10),
        PARTITION p1 VALUES LESS THAN (20)
    );
   

5. テーブル tbl2 に2件のデータを挿入します。

   obclient [SYS]> INSERT INTO tbl2 (col1, col2) VALUES (0, 22);
                   INSERT INTO tbl2 (col1, col2) VALUES (18, 6);
   

6. テーブル tbl2 のパーティション p0 と p1 にデータが挿入されているかをクエリします。

   obclient [SYS]> SELECT * FROM tbl2 PARTITION (p0);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | COL1 | COL2 |
    +------+------+
    |    0 |   22 |
    +------+------+
   

   obclient [SYS]> SELECT * FROM tbl2 PARTITION (p1);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | COL1 | COL2 |
    +------+------+
    |   18 |   6 |
    +------+------+
   

7. insert overwrite を使用して、テーブル tbl1 のパーティションp1のデータをテーブル tbl2 に上書き書き込みます。

   obclient [SYS]> INSERT INTO tbl2 PARTITION (p1) SELECT * FROM tbl1 PARTITION (p1);
   

8. テーブル tbl2 のパーティションp1のデータが上書きされているか検証します。

   obclient [SYS]> SELECT * FROM tbl2 PARTITION (p1);
   

   クエリ結果は以下のとおりです：

    +------+------+
    | col1 | col2 |
    +------+------+
    |   15 |    2 |
    +------+------+
   

   結果は、テーブル tbl2 のパーティション p1 のデータが置き換えられたことを示しています。

MERGE INOステートメントを使用してデータの挿入

バッチ処理タスクでは、ターゲットテーブルに対して大量の INSERT 操作を実行する必要がある場合があります。挿入するデータがテーブルレコードに存在しない場合でも、MERGE INTO ステートメントを使用してソーステーブルのレコードをターゲットテーブルに挿入することもできます。具体的な操作と例については、データの置換を参照してください。

DBLinkを使用してINSERTステートメントでデータを挿入する

現在、OceanBaseデータベースは、DBLinkを介してOceanBaseデータベース（Oracleモード）とOracleデータベースへのデータ書き込みをサポートしています。

DBLinkを使用して、リモートデータベースのテーブル t1 にデータ (11,11) を挿入する例を以下のとおりです：

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set

obclient> INSERT INTO t1@ob_dblink VALUES (11,11);
Query OK, 1 row affected

obclient> commit;
Query OK, 0 rows affected

obclient> SELECT * FROM t1@ob_dblink;
+------+------+
| C1   | C2   |
+------+------+
|    1 |    1 |
|   11 |   11 |
+------+------+
2 rows in set

OceanBaseデータベースのOracleモードは、ローカルテーブルデータを（INSERT、DELETE、UPDATE、および MERGE INTO）リモートテーブルに書き込むことをサポートしています。詳細については、DBLinkを使用してリモートデータベースのデータを変更するを参照してください。

関連ドキュメント

• データの更新

• データの削除

• データの置換

• DBLinkを使用してリモートデータベースのデータを変更する