Magic xpi 4.13技術情報
4. データマッパー UPSERTの使い方

1.1 UPSETとは

• Magic xpi のデータマッパーで送り先にDatabaseを配置し、そのプロパティのUPSERTをYesに設定すると、テーブルに対してUPSERT処理を行う事ができます。UPSERTとは、「データがあればUPDATE、なければINSERTする」という処理です。各DBMSにより、UPSERT用のSQLが用意されており、そのSQLをMagic xpiのデータマッパーで　発行することで、UPSERT処理が実現されます。

• UPSERTとは、データの新規挿入（INSERT）ができれば挿入を行い、新規挿入ができなければ更新（UPDATE）を行います。

1.2 サポートするデータベース

• DatabaseTriggerがサポートするデータベースは下記の通りです。

1.3 UPSERTの仕組み

• 各DBMSで用意されている、UPSERT用のSQL文を組み立て、マップで線を引くことで、UPSERT用のSQLが発行されるように組み込みます。
• Microsoft SQLServer 、 Oracle 、 DB2/400の場合、MERGE文を使います。
• MERGE文の基本構文：

 MERGE INTO 主表 USING 副表 ON (条件)

   WHEN MATCHED THEN

     UPDATE SET 列1 = 値1 [, 列2 = 値2 …]

   WHEN NOT MATCHED THEN

     INSERT (列1 [, 列2 …]) VALUES (値1 [, 値2 …])

2.1 データマッパー：送り先 DataBaseのプロパティ UPSERT

• データマッパーの送り先にDatabaseを配置し、そのプロパティのUPSERTをYesに変更します。
• データベースウィザード機能を利用して、SQL文を生成します。

（DBMSに則したUPSERT用SQL文の生成を支援してくれます）

2.2 データベースウィザード

• データベース ウィザード画面に従って進めると、各DBMSに則したUPSERT用のSQL文が生成されます。

　　　次ページから、データベースウィザードの操作画面を説明します。

• データベースがMicrosoft SQLServerの場合の操作画面例：

• データベースがMicrosoft SQLServerの場合の操作画面例：

• データベースがMicrosoft SQLServerの場合の操作画面例：

2.3 データベースウィザードで生成されたSQL文の修正

• データベースウィザードで生成されたSQL文について（Microsoft SQLServerの場合の例）

　　　MERGE文の構文との対比

生成されたSQL文

MERGE INTO dbo.[TESTTable1] USING (SELECT 1 [one]) AS dummy([one]) ON dbo.[TESTTable1].[ID] = <!?ID?!>
WHEN NOT matched THEN
INSERT ( [ID],[String1],[Date1],[Time1],[Log1] ) VALUES ( <!?[ID]?!>,<!?[String1]?!>,<!?[Date1]?!>,<!?[Time1]?!>,<!?[Log1]?!> )
WHEN matched THEN
UPDATE SET [ID]=<!?[ID]?!>,[String1]=<!?[String1]?!>,[Date1]=<!?[Date1]?!>,[Time1]=<!?[Time1]?!>,[Log1]=<!?[Log1]?!>

MERGE文の構文

MERGE INTO 主表 USING 副表 ON (条件)
WHEN NOT MATCHED THEN
INSERT (列1 [, 列2 …]) VALUES (値1 [, 値2 …])
WHEN MATCHED THEN
UPDATE SET 列1 = 値1 [, 列2 = 値2 …]

副表はダミーテーブルを使用。
（送り元のタイプがFlatFileや他のデータベースのテーブルなどに対処が可能）
条件はON句で主表の主キー列との条件式を構成する。（<!?[カラム名]?!>を記述する）
※最終的にこの構造パターンに組み立てるのは、Microsoft SQLServerも、Oracleも、DB2/400も同様です。

• SQL文の修正（Microsoft SQLServerの場合）

MERGE INTO [TESTTable1] USING (SELECT 1 [one]) AS dummy([one]) ON [TESTTable1].[ID] = <!?ID?!> WHEN NOT matched THEN
INSERT ( [ID],[String1],[Date1],[Time1],[Log1] ) VALUES ( <!?[ID]?!>,<!?[String1]?!>,<!?[Date1]?!>,<!?[Time1]?!>,<!?[Log1]?!> )
WHEN matched THEN
UPDATE SET [String1]=<!?[String1]?!>,[Date1]=<!?[Date1]?!>,[Time1]=<!?[Time1]?!>,[Log1]=<!?[Log1]?!>

UPDATE文のSET箇所の主キーカラムの削除（主キーを更新しない：[ID]）。
必要に応じて、スキーマ名の省略（「dbo.」の削除）。
省略すると、一般的にはログオンユーザと同じスキーマまたはデフォルトのスキーマが適用されます。

• SQL文の修正（Oracleの場合）

MERGE INTO XPI.”TESTTABLE1″ USING SYS.dual ON ( XPI.”TESTTABLE1″.”ID”= <!?ID?!> )
WHEN MATCHED THEN
UPDATE SET “STR1″=<!?”STR1″?!>,”DATE1″=<!?”DATE1″?!>,”LOG1″=<!?”LOG1”?!>
WHEN NOT MATCHED THEN
INSERT ( “ID”,”STR1″,”DATE1″,”LOG1″ ) VALUES ( <!?”ID”?!>,<!?”STR1″?!>,<!?”DATE1″?!>,<!?”LOG1″?!> )

UPDATE文のSET箇所の主キーカラムの削除（主キーを更新しない：”ID”）。
必要に応じて、スキーマ名の省略（「XPI.」の削除 / SYS.dual の「SYS.」も削除（省略）してもOK）。
省略すると、一般的にはログオンユーザと同じスキーマまたはデフォルトのスキーマが適用されます。

• SQL文の修正（DB2/400の場合）

データベースウィザードで生成されるSQL文の構文パターン
MERGE INTO <ライブラリ名>.<テーブル名（ファイル名）> AS tgt
USING ( SELECT [カラム] , [カラム … ] FROM <ライブラリ名>.<テーブル名（ファイル名）> ) AS src
ON ( tgt.<主キーとなるカラム名> = <!?[カラム名]?!> )
WHEN MATCHED THEN
UPDATE SET [カラム名]=<!?[カラム名]?!> [, カラム名=<!?カラム名?!> …]
WHEN NOT MATCHED THEN
INSERT ( [カラム名] [, カラム名 … ]) VALUES ( <!?[カラム名]?!> [,<!?カラム名?!> …] )

USING の副表と主表が同じであるため、DB2/400では、実行時にエラーとなることが確認されています。
USINGはダミーテーブルを利用するよう修正します。
修正例１： VALUES(‘DUMMY’)
修正例２： SELECT * FROM (VALUES(‘DUMMY’)) AS T1　
（注：SELECTでVALUESを扱う場合、AS句で別名を指定する必要があります。指定しないとエラーとなります。）

UPDATE文のSET箇所の主キーカラムの削除（主キーを更新しない）。

• 修正する場合のその他の留意点
  • ON句の条件で使用するカラムは、主キーはユニークキーまたはプライムキーであること。
  • 主キーが複合主キー（複数のカラムの組み合わせで構成）の場合は、ON句にすべてのカラムの条件式を記述する

（[複合主キーカラム１] = <!?[カラム1]?!> , [複合主キーカラム2] = <!?[カラム2]?!> [, …]）