テーブル作成後、INSERT ステートメントやその他のステートメントを使用して、テーブルに行レコードを挿入できます。本記事では、関連するステートメントの使い方と例を紹介します。
データ挿入の準備
データを挿入する前に、以下の事項を確認してください：
データベースのOracle互換モードテナントに接続されていることを確認してください。データベースへの接続操作については、接続方法の概要をご参照ください。
説明
現在ログインしているテナントが属するテナントモードは、
sysテナントでoceanbase.DBA_OB_TENANTSビューをクエリすることで確認できます。操作対象のテーブルに対する
INSERT権限を持っていることを確認してください。現在のユーザー権限に関する操作については、テナントアカウント管理をご参照ください。
INSERT INTOステートメントを使用したデータの挿入
INSERTステートメントを使用し、以下の点を参考にテーブルにデータを挿入してください。
INSERT INTOステートメントの構文は以下のとおりです：
INSERT INTO table_name (list_of_columns) VALUES (list_of_values);
パラメータ |
必須 |
説明 |
|---|---|---|
| table_name | はい | データを挿入するテーブルを指定します |
| (list_of_columns) | いいえ | データを挿入する列を指定します |
| (list_of_values) | はい | list_of_columnsで指定した列に対応する値です。値は列と1対1で対応している必要があります。 |
データ挿入の推奨事項
データを挿入する前に、列の型、有効な値、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 select_stmt;
パラメータ |
説明 |
|---|---|
| PARALLEL(N) | オプションです。上書き操作の並列実行レベルを指定します。指定しない場合、デフォルトで並列度2が採用されます。 |
| table_name | 挿入するテーブル名を指定します。 |
| select_stmt | SELECT 句を指定します。クエリステートメントの詳細については、SIMPLE 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の例
以下のSQLを実行して、自動トランザクションコミットモードを有効にします。
SET autocommit = on;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);テーブル
source_tbl1にサンプルデータを挿入します。INSERT INTO source_tbl1 VALUES (1, 'A1', 30),(2, 'B2', 25),(3, 'C3', 22);テーブル
target_tbl1にサンプルデータを挿入します。INSERT INTO target_tbl1 VALUES (4, 'D4', 35),(5, 'E5', 28);テーブル
target_tbl1のデータをクエリします。SELECT * FROM target_tbl1;実行結果は次のとおりです：
+------+------+------+ | COL1 | COL2 | COL3 | +------+------+------+ | 4 | D4 | 35 | | 5 | E5 | 28 | +------+------+------+ 2 rows in setINSERT OVERWRITE SELECTステートメントを使用し、col3が25より大きい条件でsource_tbl1からデータを選択し、それらのデータをtarget_tbl1に挿入して既存の内容を上書きします。INSERT OVERWRITE target_tbl1 SELECT * FROM source_tbl1 WHERE col3 > 25;データ上書き後のテーブル
target_tbl1の内容を確認します。SELECT * FROM target_tbl1;実行結果は次のとおりです：
+------+------+------+ | COL1 | COL2 | COL3 | +------+------+------+ | 1 | A1 | 30 | +------+------+------+ 1 row in set
MERGE INOステートメントを使用したデータの挿入
バッチ処理タスクでは、ターゲットテーブルに対して多数の INSERT 操作を実行する必要が生じる場合があります。挿入対象のデータがテーブルレコードに存在しない場合でも、MERGE INTO ステートメントを使用してソーステーブルのレコードをターゲットテーブルに挿入できます。具体的な操作手順と例については、データの上書きをご参照ください。
説明
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を使用してリモートデータベースのデータを変更するをご参照ください。