データの挿入

テーブル作成後、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 列はNULLを含めてはならず、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ステートメントを使用したデータのダイレクトロードの制限事項セクションを参照してください。
• このステートメントでダイレクトロードHintを指定すると、エラーが発生します。
• PDML (Parallel Data Manipulation Language、並列データ操作言語)フレームワークの制限により、PDMLがサポートしないシナリオではデータをインポートできません。INSERT OVERWRITE SELECT はnot supportedとエラーが表示されます。並列DMLの詳細については、並列DMLを参照してください。

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

機能紹介

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

制限事項

• 指定されたパーティションは既に存在している必要があります。存在しない場合、エラーが発生します。
• フルダイレクトロードに基づいています。/*+ append */ Hintを追加する必要はありません。追加すると、使用時にエラーが発生します。
• このステートメントは、マルチ行トランザクションでは使用できません。操作をスムーズに進めるために、事前に 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を使用してリモートデータベースのデータを変更する