データの挿入

テーブル作成後、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を使用してリモートデータベース内のデータを変更する