INSERT (SQL)

単一行の挿入

• INSERT INTO table (column, column2, ...) VALUES (value, value2, ...) 指定したテーブルの列に、値の行を挿入します。VALUES 節の値は、列のリスト内の列の名前と位置的に対応する必要があります。

  既定では、INSERT は、全か無かのイベントです。1 行すべてが挿入されるか、まったく挿入されないかのいずれかです。InterSystems IRIS® は、SQLCODE ステータス変数を返し、INSERT の成功もしくは失敗を示します。テーブルに行を挿入するには、その挿入が table、column、および value 引数で記述されたすべての要件を満たす必要があります。

  以下の文は、新しい行を Sample.Records テーブルに挿入し、StatusDate 列の値を '05/12/22' に、Status 列の値を 'Purged' に設定します。

  INSERT INTO Sample.Records (StatusDate,Status) VALUES ('05/12/22','Purged')

  

  例 ： 指定した値を使用して、行をテーブルに挿入する

• INSERT INTO table VALUES (value, value2, ...) は、テーブルの値の行を列番号順に挿入します。データ値は、定義されている列リストと位置的に対応する必要があります。指定可能なすべてのテーブル列に値を指定する必要があります。定義済みの既定値を使用することはできませんが、空の文字列を値として指定することはできます。RowID 列は指定可能ではないため、RowID 値は VALUES リストに含めないでください。

  以下の文は、4 つの値を持つ行を順に Sample.Address テーブルに挿入します。この文では、テーブルに Street、City、State、ZipCode など、ちょうど 4 つの列が含まれ、それらのデータが挿入される値に対応していると想定しています。

  INSERT INTO Sample.Address VALUES ('22 Main St.','Anytown','PA','65342')

  

  例 ： 指定した値を使用して、行をテーブルに挿入する

• INSERT INTO table SET column = value, column2 = value2, ... は、特定の列の値を明示的に設定することにより、値の行を挿入します。

  以下の文は、INSERT INTO table (column, column2, ...) VALUES (value, value2, ...) 構文と同じ操作を実行します。

  INSERT INTO Sample.Records SET StatusDate='05/12/22',Status='Purged'

  

  例 ： 指定した値を使用して、行をテーブルに挿入する

• INSERT INTO table DEFAULT VALUES は、既定の列値のみを含む行をテーブルに挿入します。

  • 定義済みの既定値を持つ列は、その値に設定されます。

  • 定義された既定値がない列は、NULL に設定されます。

  以下の文は、既定の列値を持つ行を Sample.Person テーブルに挿入します。

  INSERT INTO Sample.Person DEFAULT VALUES

  

  NOT NULL 制約を指定して定義され、DEFAULT が定義されていない列の場合、この操作は SQLCODE -108 で失敗します。

  UNIQUE 制約を指定して定義されている列は、この文を使用して挿入できます。列が DEFAULT 値なしで UNIQUE 制約を指定して定義されている場合は、DEFAULT VALUES クラスを繰り返すことで、この UNIQUE 列が NULL に設定されている行が複数挿入されます。列が UNIQUE 制約と DEFAULT 値を指定して定義されている場合、この文は 1 回しか使用できません。2 回目の呼び出しは、SQLCODE -119 エラーで失敗します。

  DEFAULT VALUES は、RowID 列、IDENTITY 列、SERIAL (%Counter) 列、ROWVERSION 列などのカウンタ列に、システムで生成された整数値を含む行を挿入します。

• INSERT INTO table VALUES :array() は、ホスト変数として指定された配列の値を、テーブルの列に挿入します。この構文は、埋め込み SQL でのみ使用できます。この配列の値は、暗黙的に、列番号順に行の列に対応している必要があります。指定可能な各列に値を指定する必要があります。列順を使用する INSERT では、定義された列の既定値を取ることはできません。

  以下の文は、実行時に配列に値を移入し、挿入する列の指定を実行時まで遅らせることができます。その他すべての挿入では、INSERT を準備する際に、挿入する列を指定する必要があります。リンク・テーブルでこの構文を使用することはできません。これを実行しようとすると、SQLCODE -155 エラーが返されます。

  このクラス・メソッドは、埋め込み SQL を使用して、配列を Sample.FullName テーブルに挿入します。myarray(1) は、RowID 列用に予約されているため、指定されません。

  ClassMethod EmbeddedSQLInsertHostVarArray()
  {
    set myarray(2)="Juanita"
    set myarray(3)="Pybus"
    &sql(INSERT INTO Sample.FullName VALUES :myarray())
    if SQLCODE '= 0 {
      write !, "Insert failed, SQLCODE= ", SQLCODE, ! ,%msg
      quit
    }
    write !,"Insert succeeded" quit
  }

  

  ホスト変数と配列については、"添え字付き配列としてのホスト変数" を参照してください。

  例 ：

  • 埋め込み SQL を使用してデータを挿入する

  • ストリーム・データをテーブルに挿入する

複数行の挿入

• INSERT INTO table query は、SELECT クエリの結果セットから取得したデータの行を挿入します。結果セット内の列は、テーブル内の列と一致する必要があります。INSERT を SELECT と共に使用することで、別のテーブルから抽出した既存のデータをテーブルに移入できます。

  以下の文は、ソース・テーブル Sample.SrcTable と同じ値をテーブル Sample.DupTable に移入します。この 2 つのテーブルの列数、列名、および列順は同じである必要があります。

  INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable

  

  例 ： SELECT クエリを使用して別のテーブルからデータを挿入する

• INSERT INTO table (column, column2, ...) query は、クエリ結果セットのデータの行を、指定された列に挿入します。INSERT 文は、行内の指定されていない列値を NULL または既定値に設定します。

  以下の文は、Sample.Person の Name および DOB 列のクエリ結果セットのデータを、Sample.Kids テーブルの一致する列に挿入します。

  INSERT INTO Sample.Kids (Name,DOB) SELECT Name,DOB FROM Sample.Person WHERE Age <= 18

  

  例 ： SELECT クエリを使用して別のテーブルからデータを挿入する

挿入オプション

• INSERT table ... は、INTO キーワードを省略します。

• INSERT %keyword [INTO] table ... は、1 つまたは複数の %keyword オプションを空白で区切って設定します。有効なオプションは、%NOCHECK、%NOFPLAN、%NOINDEX、%NOJOURN、%NOLOCK、%NOTRIGGER、%PROFILE、および %PROFILE_ALL です。

table

挿入を実行するテーブルまたはビューの名前。テーブル名またはビュー名は修飾 (schema.table)、未修飾 (table) のどちらでもかまいません。未修飾の名前は、スキーマ検索パス (指定されている場合)、または既定のスキーマ名を使用して、そのスキーマと照合されます。

table 引数の代わりにサブクエリを使用して INSERT を実行することもできます。以下に例を示します。

INSERT INTO (SELECT column1 AS c1 FROM MyTable) (c1) VALUES ('test')

行をテーブルに挿入するには、適切なテーブルレベルの特権が必要です。

column

列名、または列名のコンマ区切りのリスト。後者の場合は、値のリストに対応した順序で指定します。省略した場合、値のリストは列番号順にすべての列に適用されます。

行をテーブルに挿入するには、適切な列レベルの特権が必要です。

value

column 内の対応する列のデータ値を指定する、VALUES 節で指定されるスカラ式またはコンマ区切りのスカラ式のリスト。指定した値の数が列数より少ない場合、SQLCODE -62 エラーが生成されます。指定した値の数が列数より多い場合、SQLCODE -116 エラーが生成されます。

テーブルに行を挿入するには、value で指定される列値が、以下の要件を満たしている必要があります。

• 各列の値がデータ型の妥当性検証に合格する必要があります。不適切な列値を列のデータ型に挿入しようとすると、SQLCODE -104 エラーが返されます。この要件は挿入されるデータ値にのみ適用されます。DEFAULT 値を取る列は、データ型の検証またはデータ・サイズの検証に合格する必要はありません。適切であるかどうかは、挿入されるデータ値のデータ型ではなく、列のデータ型によって決まります。例えば、日付列に文字列値を挿入しようとすると、現在のモードでその文字列が日付の検証に合格しない限り失敗します。ただし、文字列の列に日付値を挿入しようとした場合は成功します。INSERT は、日付をリテラル文字列としてテーブルに挿入します。データを挿入先のデータ型に変換するには、CONVERT 関数を使用します。

• 各データ値は、その列の MAXLEN、MAXVAL、および MINVAL の範囲内でなければなりません。例えば、VARCHAR(24) として定義された列に 24 文字より長い文字列を挿入しようとしたり、TINYINT として定義された列に 127 より大きい数値を挿入しようとすると、SQLCODE -104 エラーが発生します。

• ODBC または JDBC を介して無効な DOUBLE 数値を指定すると、SQLCODE -104 エラーが発生します。

• 挿入されるデータ値は表示モードから論理モードへの変換に合格する必要があります。InterSystems SQL では、データを logical モード形式で格納します。一部のデータ型では、論理形式が表示形式と異なることがあります。例えば、日付データは日数を示す整数値として格納され、時刻データは午前 0 時 00 分からの秒数として格納され、%List データはエンコードされた文字列として格納されます。文字列や数値など、その他のデータ型の場合、変換は必要ありません。論理格納値に変換できない形式で値を挿入しようとすると、エラーが発生します。モード変換の詳細は、"データ表示オプション" を参照してください。

• 各データ値は、挿入先の列のデータ制約の妥当性検証に合格する必要があります。

  • NOT NULL として定義された列には、データ値が与えられる必要があります。DEFAULT 値のない列の場合、データ値を指定しないと、SQLCODE -108 エラーが発生します。

  • データ値は、列または列のグループで定義された UNIQUE データ制約に従う必要があります。UNIQUE 列 (主キー列など)、または UNIQUE 列 グループに重複値を挿入しようとすると、SQLCODE -119 エラーが発生します。このエラーは、値を指定せず、その列の DEFAULT の 2 回目の使用により重複値が指定される場合にも発生します。

  • VALUELIST パラメータで永続クラス・プロパティとして定義された列は、VALUELIST でリストされた値のみを受け入れることができ、それ以外の場合は値なし (NULL) となります。VALUELIST の値では、大文字と小文字が区別されます。VALUELIST の値と一致しないデータ値を指定すると、SQLCODE -104 の列のエラーが発生します。

• 数値はキャノニック形式で挿入されますが、先頭と末尾の 0 や、先頭の複数の符号を付けて指定できます。ただし、SQL では、2 つの連続したマイナス記号は、1 行コメント文字として解析されます。したがって、先頭に 2 つの連続したマイナス符号を付けて数値を指定しようとすると、SQLCODE -12 エラーになります。

• 既定では、システム生成値が格納される RowID、IDKey、IDENTITY などの列を、値の挿入対象に指定することはできません。既定では、これらの列のいずれかに非 NULL 値を挿入すると、SQLCODE -111 エラーが返されます。これらの列のいずれかに NULL を挿入すると、InterSystems IRIS はその NULL をシステム生成値でオーバーライドします。エラーは生成されません。

  • テーブルで ROWVERSION 列を定義すると、行が挿入された場合、その列にはシステムで生成されたカウンタ値が自動的に割り当てられます。ROWVERSION 列に値を挿入すると、SQLCODE -138 エラーが返されます。

  • ユーザが指定した値を受け取る IDENTITY 列を作成することができます。SetOption("IdentityInsert")Opens in a new tab メソッドを設定すると、IDENTITY 列の既定の制約をオーバーライドし、IDENTITY 列に一意の整数値を挿入できるようになります。この制約の現在の設定を返すには、GetOption("IdentityInsert")Opens in a new tab メソッドを呼び出します。IDENTITY 列に値を挿入すると IDENTITY カウンタが変更され、後続のシステム生成値が、このユーザ指定の値からインクリメントを開始するようになります。IDENTITY 列に NULL を挿入すると、SQLCODE -108 エラーが発生します。

  • IDKey データには次の制限があります。1 つのインデックスに複数の IDKey 列がある場合は、列の区切り文字列として “||” (二重の垂直バー) が使用されています。したがって、IDKey 列にこの文字列を使用したデータ値を挿入することはできません。

• INSERT コマンドで %NOCHECK キーワードが指定されているか、外部キーが NOCHECK キーワードで定義されていることがない限り、挿入される値は、外部キーの参照整合性に違反してはなりません。外部キーの参照整合性に違反する挿入が試みられると、SQLCODE -121 エラーが発生します。テーブルの外部キー制約および外部キー制約の名前のリストの詳細は、[カタログの詳細] の [制約] を参照してください。

• データ値をサブクエリにすることはできません。サブクエリを列値に指定しようとすると、SQLCODE -144 エラーが返されます。

非表示の文字値

非表示文字を含む値を挿入するには、CHAR 関数と連結演算子を使用します。例えば以下の文では、文字 “A”、改行文字、および文字 “B” で構成される文字列が挿入されます。

INSERT INTO MyTable (Text) VALUES ('A'||CHAR(10)||'B')

関数の結果を連結するには、ObjectScript で使用される _ 連結演算子ではなく、|| 連結演算子を使用する必要があります。

query

その結果セットが、column で指定された対応する列にデータ値を指定する SELECT クエリ。

SELECT クエリは 1 つ以上のテーブルから列データを抽出し、INSERT コマンドはこの列データを含むテーブル内に、対応する新しい行を作成します。挿入されるデータがテーブル列に収まることができる限り、対応する列は、さまざまな列名および列長を持つことができます。対応する列がデータ型および長さ検証チェックに合格しない場合、InterSystems SQL は SQLCODE -104 エラーを生成します。

挿入される行の数を制限するには、SELECT 文内で TOP 節を指定します。クエリがこれらの上位行のうちどれを選択するかを決定するには、SELECT 文で ORDER BY 節を使用します。

特定の列の一意の値のみを挿入するには、クエリで GROUP BY 節を指定します。既定では、GROUP BY により、値がグループ化のために大文字に変換されます。挿入された値の大文字/小文字を維持するには、クエリで %EXACT 照合を指定します。以下に例を示します。

INSERT INTO Sample.UniquePeople (Name,Age)
     SELECT Name,Age FROM Sample.Person
     WHERE Name IS NOT NULL GROUP BY %EXACT Name

SELECT で INSERT を使用すると、%ROWCOUNT 変数は挿入した行の数に設定されます (0 または正の整数)。

array

ホスト変数として指定する値の動的なローカル配列。この値は、埋め込み SQL にのみ適用されます。

配列の最下位の添え字は指定しないでください。:myupdates()、:myupdates(5,)、および :myupdates(1,1,) はすべて、有効な指定になります。

%keyword

INSERT 処理を構成するキーワード・オプション。キーワード・オプションは順不同で指定できます。複数のキーワード・オプションは、スペースで区切ります。

以下の表で、指定できるキーワード・オプションについて説明します。

指定した値を使用して、行をテーブルに挿入する

この例では、新しい値の行をテーブルに挿入するさまざまな方法を示します。

会社データを含むテーブルを作成します。このテーブルには、会社名 (一意である必要があります) と本社がある国という、2 つの必須列があります。2 番目の列、Revenue (収益) は必須ではなく、既定値 0 を持ちます。

CREATE TABLE Sample.Company (
    Name VARCHAR(20) UNIQUE NOT NULL,
    Revenue INTEGER DEFAULT 0,
    Country VARCHAR(10) NOT NULL)

テーブルにデータ行を挿入します。列の値は、テーブルの列順と同じ順序で指定する必要があります。

INSERT INTO Sample.Company VALUES ('CompanyA',10000,'BEL')

別のデータ行を、今度は挿入する列名を指定して挿入します。この文は、Revenue 列を省略しているため、InterSystems SQL はこの列の値を既定値 0 に設定します。

INSERT INTO Sample.Company (Name,Country) VALUES ('CompanyB','CAN')

3 行目のデータを、今度は column=value 構文を使用して挿入します。column=value ペアは、テーブル列の順序である必要はありません。

INSERT INTO Sample.Company Set Name = 'CompanyC', Country = 'ECU', Revenue = 25000

挿入されたデータを Revenue 順に表示します。

SELECT * FROM Sample.Company ORDER BY Revenue DESC

完了したら、テーブルを削除します。

DROP TABLE Sample.Company

ストリーム・データをテーブルに挿入する

以下の例は、埋め込み SQL を使用して、ストリーム・フィールドに挿入できるさまざまなデータ値タイプを表示します。

任意のテーブルで、文字列リテラル、または文字列リテラルが含まれるホスト変数を挿入できます。以下に例を示します。

    set literal="Technique 1"
    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:literal))

シャード化されていないテーブルのストリーム・オブジェクトに、オブジェクト参照 (OREF) を挿入することもできます。InterSystems IRIS は、このオブジェクトを開いて、そのコンテンツを新しいストリーム・フィールドにコピーします。例えば以下のようになります。

    set oref=##class(%Stream.GlobalCharacter).%New()
    do oref.Write("Technique non-shard 1")

    //do the insert; use an actual OREF
    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:oref))

あるいは、ストリーム・オブジェクトに OREF の文字列バージョンを挿入することもできます。

    set oref=##class(%Stream.GlobalCharacter).%New()
    do oref.Write("Technique non-shard 2")

    //next line converts OREF to a string OREF
    set string=oref_""

    //do the insert
    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:string))

シャード・テーブルの場合は、^IRIS.Stream.Shard グローバルに保存されている一時ストリーム・オブジェクトを使用して、オブジェクト ID (OID) を挿入できます。

    set clob=##class(%Stream.GlobalCharacter).%New("Shard")
    do clob.Write("Technique Sharded Table 1")
    set sc=clob.%Save() // Handle $$$ISERR(sc)
    set ClobOid=clob.%Oid()

    &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:ClobOid))

適切に定義されていないストリーム値を挿入すると、SQLCODE -412 エラーが返されます。

詳細および例は、"ストリーム・データ・フィールドへのデータの挿入" を参照してください。

埋め込み SQL を使用してデータを挿入する

この埋め込み SQL の例では、ホスト変数配列を使用して 3 つの列値を持つ行を挿入しています。配列要素は列順に番号付けされます。指定される配列値は、2 番目の要素 (この場合、company(2)) で始まる必要があります。最初の配列要素は、RowID 列に対応します。これは自動的に提供され、定義できません。

  SET company(2)="Company1"
  SET company(3)=15000
  SET company(4)="JPN"
  &sql(INSERT INTO Sample.Company VALUES :company())

この埋め込み SQL の例では、実行時に INSERT に値の配列を渡すために、最後の添え字が未定義の動的なローカル配列を使用します。

  NEW SQLCODE,%ROWCOUNT,%ROWID
  &sql(INSERT INTO Sample.Employee VALUES :emp('profile',))
  WRITE !,"SQL Error code: ",SQLCODE," Row Count: ",%ROWCOUNT

前の文により、挿入された "Employee" 行の各列は次のように設定されます。ここで "col" は Sample.Employee テーブルの列番号です。

emp("profile",col)

ダイナミック SQL を使用してデータを挿入する

このクラス・メソッドでは、ダイナミック SQL を使用し、メソッドに渡された引数に基づいてテーブルに値を挿入します。

ClassMethod DynamicSQLInsert(name As %String, revenue As %Integer, country As %String)
{
  set sqltext = "INSERT INTO Sample.Company (Name,Revenue,Country) VALUES (?,?,?)"

  set tStatement = ##class(%SQL.Statement).%New(0,"Sample")
  set qStatus = tStatement.%Prepare(sqltext)
    if qStatus'=1 {write "%Prepare failed:" DO $System.Status.DisplayError(qStatus) quit}
  set rtn = tStatement.%Execute(name,revenue,country)
  if rtn.%SQLCODE=0 {
    write !,"Insert succeeded"
    write !,"Row count=",rtn.%ROWCOUNT
    write !,"Row ID=",rtn.%ROWID }
  elseif rtn.%SQLCODE=-119 {
    write !,"Duplicate record not written",!,rtn.%Message  quit }
  else {  write !,"Insert failed, SQLCODE=",rtn.%SQLCODE  }
}

SELECT クエリを使用して別のテーブルからデータを挿入する

この例では、INSERT を SELECT と共に使用して、別のテーブルから抽出したデータをテーブルに移入する方法を示します。この例では、Name、DOB、および Age 列を含む Sample.Person テーブルを既に定義していることを前提としています。このようなテーブルは、GitHub (https://github.com/intersystems/Samples-DataOpens in a new tab) からダウンロードできます。ダウンロードの手順は、"インターシステムズ製品で使用するサンプルのダウンロード" を参照してください。

MyStudents というテーブルを作成します。このテーブルには、名前と生年月日の列 (どちらも指定します)、および年齢の列 (生年月日の列から計算されます) が含まれます。

CREATE TABLE MyStudents (
  StudentName VARCHAR(32),
  StudentDOB DATE,
  StudentAge INTEGER COMPUTECODE {set {StudentAge} =
                       $piece(($piece($horolog,",",1)-{StudentDOB})/365,".",1)}
                       CALCULATED)

Sample.Person テーブルからの学生データを MyStudents テーブルに挿入します。21 歳以下の人を選択する SELECT クエリを使用します。以下の 2 つのクエリのいずれかを使用できます。2 つのテーブルの列順は一致しており、格納されるのは 2 つの列のみであるため、列名は省略できます。

INSERT INTO MyStudents (StudentName,StudentDOB)
  SELECT Name,DOB
  FROM Sample.Person WHERE Age <= 21

INSERT INTO MyStudents
  SELECT Name,DOB
  FROM Sample.Person WHERE Age <= 21

年齢順に結果を表示します。

SELECT * FROM MyStudents ORDER BY StudentAge

完了したら、テーブルを削除します。

DROP TABLE MyStudents

INSERT を SELECT と共に使用して、既存のテーブルから重複テーブルを作成することもできます。この操作を使用して、既存のデータを再定義されたテーブルにコピーでき、元のテーブルでは有効にならなかったであろう将来の列データ値がこのテーブルで受け入れられます。詳細は、"重複テーブルにデータをコピーする" を参照してください。

テーブルレベルの特権

テーブルに 1 つ以上のデータ行を挿入するには、ユーザ (または指定したユーザ) に、そのテーブルに対するテーブルレベルの特権か列レベルの特権が必要です。

• テーブルにデータを挿入する際は、そのテーブルに対する INSERT 特権が必要です。

• SELECT クエリを使用して別のテーブルからデータを挿入する場合、ユーザはそのテーブルに対する SELECT 特権を持っている必要があります。

テーブルの所有者 (作成者) にはそのテーブルに対するすべての特権が自動的に付与されます。所有者でない場合は、そのテーブルに対する特権が付与される必要があります。これを実行しないと、SQLCODE -99 エラーが返されます。

適切な特権があるかどうかを確認するには、%CHECKPRIV を使用します。ユーザにテーブルの特権を割り当てるには、GRANT を使用します。詳細は、"特権" を参照してください。

シャード・テーブルに挿入するには、ターゲット・テーブルに対する INSERT 特権が必要です。これらの特権がない場合は、SQLCODE -253 エラーが返されます。

テーブルレベルの特権は、テーブルの全列に対して列レベルの特権を持っていることと同等ですが、まったく同じではありません。

列レベルの特権

テーブルレベルの INSERT 特権がない場合に、指定された値を列に挿入するには、その列に対する列レベルの INSERT 特権が必要です。INSERT コマンドで指定した値は、INSERT 特権を持つ列のみに挿入されます。

指定した列に対して列レベルの INSERT 特権がない場合、InterSystems SQL は、列の既定値 (定義されている場合) または NULL (既定値が定義されていない場合) を挿入します。既定値のない、NOT NULL と定義された列に対してユーザが INSERT 特権を持たない場合、InterSystems IRIS は準備時に SQLCODE -99 (特権違反) エラーを発行します。

INSERT コマンドで SELECT サブクエリの WHERE 節内に列を指定する場合、以下の特権が必要です。

• データ挿入列ではない場合、それらの列に対する SELECT 特権。

• これらの列が結果セットに含まれる場合には、これらの列に対する SELECT 特権と UPDATE 特権の両方。

プロパティが ReadOnly として定義されている場合、対応するテーブル列も ReadOnly として定義されます。ReadOnly 列への値の割り当ては、InitialExpression または SqlComputed を使用した場合のみ可能です。列レベルの ReadOnly (SELECT または REFERENCES) 特権を持っている列に値を挿入しようとすると、SQLCODE -138 エラーが発生します。

適切な特権があるかどうかを確認するには、%CHECKPRIV を使用します。ユーザに列レベルの特権を割り当てるには、GRANT を使用します。詳細は、"特権" を参照してください。

行レベル・セキュリティ

InterSystems IRIS の行レベル・セキュリティにより、行セキュリティの定義で行への次回のアクセスが許可されていない場合でも、INSERT を使用して行を追加できるようになります。INSERT により行への今後の SELECT アクセスが妨げられないようにするには、WITH CHECK OPTION を持つビューで INSERT を実行します。詳細は、"CREATE VIEW" を参照してください。

ODBC データ型の処理

ODBC を使用した Fast Insert を使用する場合、整数または _int64 データ型を使用して、フィールド TIMESTAMP または POSIX タイプのフィールドを設定できます。指定された数値は $HOROLOG 値として扱われます。

倍精度値に変換される文字列値が検証され、その文字列が数値を表すことが保証されます。この検証では、"INF"、"infinity"、および "NaN" の値 (+ または - 記号が先頭に付く場合もあります) も常に受け入れます。

数値から整数に変換される値は、四捨五入ではなく、切り捨てられます。

トランザクションのアトミック性設定

既定では、INSERT、UPDATE、DELETE、および TRUNCATE TABLE はアトミック処理として実行されます。INSERT は、正常に完了するか、すべての操作がロールバックされるかのいずれかです。指定した行のいずれかを挿入できない場合、指定した行は 1 行も挿入できずにデータベースは INSERT を発行する前の状態に戻ります。

現在のプロセスに対するこの既定は、SET TRANSACTION %COMMITMODE を呼び出すことによって SQL 内で変更できます。現在のプロセスに対するこの既定は、次の構文を使用して SetOption()Opens in a new tab メソッドを呼び出すことによって ObjectScript 内で変更できます。

SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval)

以下の intval 整数オプションを使用できます。

• 1 または IMPLICIT (自動コミットがオン — 既定) — INSERT ごとに個別のトランザクションが構成されます。

• 2 または EXPLICIT (自動コミットがオフ) — 進行中のトランザクションがない場合は、INSERT コマンドによってトランザクションは自動的に開始されます。ただし、COMMIT または ROLLBACK で明示的にトランザクションを終了する必要があります。EXPLICIT モードでは、トランザクションあたりのデータベース操作の数は、ユーザ定義です。

• 0 または NONE (自動トランザクションなし) — INSERT を呼び出してもトランザクションは開始されません。INSERT 操作の失敗により、行の一部が挿入されたり挿入されなかったりすることで、データベースが整合性のない状態になる可能性があります。このモードでトランザクションのサポートを提供するには、START TRANSACTION を使用してトランザクションを開始し、COMMIT または ROLLBACK を使用してトランザクションを終了する必要があります。

  シャード・テーブルは常に、自動トランザクションなしのモードに設定されます。つまり、シャード・テーブルに対する挿入、更新、および削除はすべて、トランザクションの範囲外で実行されます。

現在のプロセスのアトミック性設定を確認するには、以下の ObjectScript の例のように、GetOption("AutoCommit")Opens in a new tab メソッドを使用します。

  SET stat=$SYSTEM.SQL.Util.SetOption("AutoCommit",$RANDOM(3),.oldval)
  IF stat'=1 {WRITE "SetOption failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET x=$SYSTEM.SQL.Util.GetOption("AutoCommit")
    IF x=1 {
      WRITE "Default atomicity behavior",!
      WRITE "automatic commit or rollback" }
    ELSEIF x=0 {
      WRITE "No transaction initiated, no atomicity:",!
      WRITE "failed DELETE can leave database inconsistent",!
      WRITE "rollback is not supported" }
    ELSE { WRITE "Explicit commit or rollback required" }

トランザクションのロックしきい値の変更

%NOLOCK キーワードを指定しない場合、INSERT、UPDATE、および DELETE 操作時に自動的にレコードに標準のロックがかかります。影響を受ける各レコード (行) は、現在のトランザクションが継続している間はロックされます。

既定のロックしきい値は、テーブルごとに 1000 ロックです。トランザクションの間にテーブルから 1000 件を超えるレコードを挿入すると、ロックのしきい値に到達し、InterSystems IRIS は自動的にロック・レベルをレコード・ロックからテーブル・ロックに上げます。これによってトランザクション時に、ロック・テーブルをオーバーフローすることなく、大規模な挿入を実行できます。

InterSystems IRIS は、以下のロック・エスカレーション策のいずれかを適用します。

• "E" タイプのロック・エスカレーション — InterSystems IRIS は、以下が当てはまる場合にこのロック・エスカレーションを使用します。

  1. テーブルのクラスで %Storage.Persistent を使用している。これは、管理ポータルの SQL スキーマ表示の [カタログの詳細] から確認できます。

  2. クラスで、IDKey インデックスが指定されていないか、単一プロパティの IDKey インデックスが指定されている。

  "E" タイプのロック・エスカレーションの詳細は、"LOCK" を参照してください。

• 従来の SQL ロック・エスカレーション — このロック・エスカレーションは、クラスにマルチプロパティの IDKey インデックスがある場合に発生する可能性があります。この場合は、%Save ごとにロック・カウンタがインクリメントされます。つまり、トランザクション内の単一オブジェクトを 1001 回保存すると、InterSystems IRIS はロックのエスカレーションを試みます。

どちらのロック・エスカレーション策の場合も、$SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab メソッドを使用して、現在のシステム全体用ロックしきい値を決定できます。既定値は 1000 です。以下のオプションのいずれかを使用してシステム全体のロックしきい値を構成できます。

• $SYSTEM.SQL.Util.SetOption("LockThreshold")Opens in a new tab メソッドを呼び出します。

• 管理ポータルで、[システム管理]、[構成]、[SQL とオブジェクトの設定]、[SQL] の順に選択します。[ロック・エスカレーションしきい値] の現在の設定を表示して編集します。既定は 1000 ロックです。この設定を変更すると、変更後に開始される新しいプロセスは、新しい設定になります。

ロックしきい値を変更するには、%Admin リソース管理に対する USE 許可を持っている必要があります。InterSystems IRIS は、ロックしきい値の変更を現在のプロセスすべてに即座に適用します。

結果として、自動ロック・エスカレーションでは、デッドロックの状況が起こる可能性があります。つまり、テーブル・ロックへのエスカレーションを試みたときに、テーブル内のレコード・ロックを保持する別プロセスとの競合が起こる可能性があります。これを回避するには、いくつかの方法が考えられます。

1. ロック・エスカレーションがトランザクション内で起こる可能性が低くなるように、ロック・エスカレーションのしきい値を上げる。

2. ロック・エスカレーションが即座に起こるように、ロック・エスカレーションのしきい値を大幅に下げる。これにより、別プロセスが同一テーブル内のレコードをロックする機会が少なくなります。

3. トランザクションが継続している間はテーブル・ロックを適用し、レコード・ロックは実行しない。これは、LOCK TABLE、UNLOCK TABLE (テーブル・ロックがトランザクションの終了まで持続するよう、IMMEDIATE キーワードはなし) の順に指定することで、トランザクション開始時に実行できます。その後、%NOLOCK オプションを使用して挿入を実行します。

自動ロック・エスカレーションは、ロック・テーブルのオーバーフローを防ぐことを目的としています。ただし、大量の挿入などを実行したために <LOCKTABLEFULL> エラーが発生した場合は、INSERT によって SQLCODE -110 エラーが発行されます。

トランザクションでのロックの詳細は、"トランザクション処理" を参照してください。

子テーブルの挿入

子テーブルへの INSERT 操作時には、親テーブル内の対応する行に共有ロックがかかります。この行は、子テーブル行の挿入中はロックされます。その後、ロックは解除されます (トランザクションの終了までロック状態が継続することはありません)。これにより、参照される親の行がこの挿入操作の間に変更されることがなくなります。

重複テーブルにデータをコピーする

列の順序が一致し、互換性のあるデータ型であれば、SELECT * と組み合わせて INSERT を使用し、あるテーブルから重複テーブルへデータをコピーできます。この操作を使用して、既存のデータを再定義されたテーブルにコピーし、元のテーブルでは有効にならなかったであろう将来の列データ値がこのテーブルで受け入れられます。以下にサンプル構文を示します。

INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable

列名が一致する必要はありませんが、コピー元のテーブルとコピー先のテーブルのデータは以下の要件を満たす必要があります。

• コピー元テーブルの値のデータ型は、コピー先テーブルの列のデータ型と互換性がある必要があります。例えば、INTEGER 値は VARCHAR に変換できるため、INTEGER 列の整数データを VARCHAR 列に挿入することは可能です。データ値に互換性がない場合、INSERT は SQLCODE -104 エラーで失敗します。挿入するデータを挿入先のデータ型に明示的に変換するには、CONVERT 関数を使用します。

• コピー元テーブルの値のデータ型の長さは、コピー先テーブルの列の長さと適合する必要があります。定義されている列データの長さが相互に一致する必要はありません。実際のデータと一致する必要があるだけです。例えば、SrcTable は FullName VARCHAR(60) 列を持ち、DupTable は対応する PersonName VARCHAR(40) 列を持つとします。40 文字を超える既存の FullName がない場合、INSERT は成功します。いずれかの FullName が 40 文字を超える場合、INSERT は SQLCODE -104 エラーで失敗します。

• 2 つのテーブルの列順には互換性がある必要があり、互換性がない場合、INSERT コマンドは SQLCODE -64 エラーで失敗します。DDL CREATE TABLE は、定義された順序で列をリストします。テーブルを定義する永続クラスはアルファベット順に列をリストします。

• テーブルには互換性のある列数が必要ですが、コピー先テーブルにはコピーされた列以外の列を含めることができます。例えば、SrcTable は、列 FullName VARCHAR(60) および Age INTEGER を持つことができ、DupTable は PersonName VARCHAR(60)、Years INTEGER、および ShoeSize INTEGER を持つことができます。

• コピー元テーブルとコピー先テーブルのいずれかでパブリック RowID が定義されている場合、データのコピーは以下の表に示すように制限されます。

  コピー元テーブル	コピー先テーブル	コピー操作の動作
  プライベート RowID	プライベート RowID	INSERT SELECT を SELECT * と共に使用して、データを重複テーブルにコピーできます。
  パブリック RowID	パブリック RowID	INSERT SELECT を使用して、データを重複テーブルにコピーすることはできません。SQLCODE -111 エラーが生成されます。
  プライベート RowID	パブリック RowID	INSERT SELECT を使用して、データを重複テーブルにコピーすることはできません。SQLCODE -111 エラーが生成されます。
  パブリック RowID	プライベート RowID	INSERT SELECT を SELECT * と共に使用して、データを重複テーブルにコピーすることはできません。一方の選択リストに RowID が存在していて選択リストが非互換になるため、SQLCODE -64 エラーが生成されます。INSERT SELECT をすべての列名のリスト (RowID は含まない) と共に使用して、重複テーブルにデータをコピーできます。ただし、コピー元に外部キーのパブリック RowID がある場合、外部キー・リレーションシップはコピー先テーブルでは保持されません。コピー先は新しいシステム生成 RowID を受け取ります。 コピー元テーブルに外部キーのパブリック RowID があり、コピー先テーブルに同じ外部キー・リレーションシップを設定したい場合は、CREATE TABLE で %CLASSPARAMETER ALLOWIDENTITYINSERT=1 を使用してコピー先テーブルを定義する必要があります。テーブルが ALLOWIDENTITYINSERT=1 として定義されている場合、この設定を SetOption("IdentityInsert")Opens in a new tab メソッドで変更することはできません。

  DDL CREATE TABLE は既定で RowID をプライベートとして定義します。テーブルを定義する永続クラスは、既定で RowID をパブリックとして定義します。これをプライベートにするには、永続クラスを定義する際に、SqlRowIdPrivate クラス・キーワードを指定する必要があります。ただし、外部キーで参照できるのはパブリック RowID を持つテーブルだけです。

コピー元またはコピー先のテーブルの永続クラスで Final キーワードが定義されている場合、このキーワードは重複テーブルへのデータのコピーに影響を与えません。

%SerialObject プロパティにデータを挿入する

%SerialObject にデータを挿入する際は、埋め込み %SerialObject を参照するテーブル (永続クラス) に挿入する必要があります。%SerialObject に直接挿入することはできません。例えば、永続クラスにプロパティ Paddress があり、これはプロパティ Street、City、Country がこの順番に含まれるシリアル・オブジェクトを参照するものとします。このプロパティの値を挿入する方法は以下のとおりです。

• 参照するフィールドを使用して、複数の %SerialObject プロパティの値を %List 構造として挿入する。以下に例を示します。

  INSERT INTO MyTable SET PAddress=$LISTBUILD('123 Main St.','Newtown','USA')

  

  INSERT INTO MyTable (PAddress) VALUES ($LISTBUILD('123 Main St.','Newtown','USA'))

  

  %List には、シリアル・オブジェクト (またはプレースホルダとしてのコンマ) のプロパティの値が、このシリアル・オブジェクトで指定されている順序で含まれる必要があります。

  このタイプの挿入では、%SerialObject プロパティ値の検証は実行されない場合があります。したがって、%List 構造を使用して %SerialObject プロパティを挿入したら、$SYSTEM.SQL.Schema.ValidateTable() メソッドを使用してテーブル・データの検証を実行してください。

• アンダースコア構文を使用して、個々の %SerialObject プロパティの値を任意の順序で挿入する。以下に例を示します。

  INSERT INTO MyTable SET PAddress_City='Newtown',PAddress_Street='123 Main St.',PAddress_Country='USA'

  

  指定されていないシリアル・オブジェクト・プロパティは、既定の NULL になります。

  このタイプの挿入では、%SerialObject プロパティ値の検証が実行されます。