INSERT (Transact-SQL)
SQL Server 2008 R2 で、1 つまたは複数の新しい行をテーブルやビューに追加します。例については、「INSERT の例 (Transact-SQL)」を参照してください。
.gif "トピック リンク アイコン")
構文
-- Standard INSERT syntax
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ] }
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n ]
| derived_table
| execute_statement
| <dml_table_source>
| DEFAULT VALUES
}
}
}
[; ]
-- Syntax for external tool only
INSERT
{
[BULK]
[ database_name . [ schema_name ] . | schema_name . ]
[ table_name | view_name ]
( <column_definition> )
[ WITH (
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] KEEP_NULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] TABLOCK ]
) ]
}
[; ]
<object> ::=
{
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_name
}
<dml_table_source> ::=
SELECT <select_list>
FROM ( <dml_statement_with_output_clause> )
[AS] table_alias [ ( column_alias [ ,...n ] ) ]
[ WHERE <search_condition> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
<data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max ]
引数
WITH <common_table_expression>
INSERT ステートメントのスコープ内で定義された、一時的な名前付き結果セット (共通テーブル式とも呼ばれる) を指定します。結果セットは SELECT ステートメントから派生します。共通テーブル式 (CTE) は、SELECT、DELETE、UPDATE、MERGE、CREATE VIEW の各ステートメントでも使用できます。詳細については、「WITH common_table_expression (Transact-SQL)」を参照してください。
TOP (expression) [ PERCENT ]
挿入するランダムな行の数または比率 (%) を指定します。expression は行数または行の比率 (%) にすることができます。INSERT、UPDATE、または DELETE と共に使用される TOP 式で参照される行は、任意の順序に並べられません。INSERT、UPDATE、および DELETE の各ステートメントで TOP を使用する場合は、expression を区切るかっこが必要です。詳細については、「TOP (Transact-SQL)」を参照してください。
INTO
INSERT キーワードと対象のテーブルとの間で使用できるキーワードで、省略可能です。server_name
テーブルまたはビューが配置されているリンク サーバーの名前です。server_name は、リンク サーバー名として指定することも、OPENDATASOURCE 関数を使用して指定することもできます。server_name をリンク サーバーとして指定した場合は、database_name および schema_name も指定する必要があります。server_name を OPENDATASOURCE で指定した場合は、database_name および schema_name がすべてのデータ ソースに適用されるとは限らず、リモート オブジェクトにアクセスする OLE DB プロバイダーの機能により制限されます。詳細については、「分散クエリ」を参照してください。
database_name
データベースの名前を指定します。schema_name
テーブルまたはビューが属するスキーマの名前を指定します。table_or view_name
データを受け取るテーブルまたはビューの名前を指定します。table 変数は、そのスコープの中では、INSERT ステートメントでテーブル ソースとして使用できます。
table_or_view_name によって参照されるビューは更新可能であることが必要です。また、そのビューの FROM 句ではベース テーブルを 1 つだけ参照している必要があります。たとえば、複数のテーブルを参照するビューに対して INSERT を実行するには、1 つのベース テーブルの列のみを参照する column_list を使用する必要があります。更新可能なビューの詳細については、「CREATE VIEW (Transact-SQL)」を参照してください。
rowset_function_limited
OPENQUERY 関数または OPENROWSET 関数を指定します。これらの関数の使用は、リモート オブジェクトにアクセスする OLE DB プロバイダーの機能により制限されます。詳細については、「分散クエリ」を参照してください。WITH ( <table_hint_limited> [...n ] )
対象のテーブルに設定可能なテーブル ヒントを 1 つ以上指定します。キーワード WITH とかっこが必要です。READPAST、NOLOCK、および READUNCOMMITTED は指定できません。テーブル ヒントの詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。
.gif "重要な注意事項")
重要INSERT ステートメントの対象となるテーブルに対して、HOLDLOCK、SERIALIZABLE、READCOMMITTED、REPEATABLEREAD、および UPDLOCK のヒントを指定する機能は、将来のバージョンの SQL Server では削除される予定です。これらのヒントは、INSERT ステートメントのパフォーマンスに影響を与えません。新しい開発作業では、これらのオプションの使用は避け、現在これらを使用しているアプリケーションは修正するようにしてください。
INSERT ステートメントの対象であるテーブルに対して TABLOCK ヒントを指定すると、TABLOCKX ヒントを指定した場合と同じ効果を得られます。テーブルに対して、排他ロックが取得されます。
(column_list)
データを挿入する 1 つ以上の列で構成されるリストを指定します。column_list はかっこで囲み、コンマで区切る必要があります。column_list に列がない場合、データベース エンジンでは、列の定義に基づいて値を設定できる必要があります。値を設定できない場合は行を読み込むことはできません。データベース エンジンは、列が次の条件を満たす場合、自動的に列に値を設定します。
IDENTITY プロパティを持っている。増分された次の ID 値が使用されます。
既定値を持っている。列の既定値が使用されます。
timestamp 型である。現在のタイムスタンプ値が使用されます。
NULL 値を許容している。NULL 値が使用されます。
計算列である。計算値が使用されます。
ID 列に値を明示的に挿入するときには、column_list と値リストを使用する必要があります。また、テーブルの SET IDENTITY_INSERT オプションは ON にする必要があります。
OUTPUT 句
挿入操作の一部として、挿入された行を返します。処理中のアプリケーションに結果を返すことも、テーブルまたはテーブル変数に結果を挿入して処理を続行することもできます。OUTPUT 句は、ローカル パーティション ビュー、分散パーティション ビュー、リモート テーブルのいずれかを参照する DML ステートメントではサポートされていません。また、execute_statement が含まれる INSERT ステートメントでもサポートされていません。OUTPUT INTO 句は、<dml_table_source> 句を含む INSERT ステートメントではサポートされません。
VALUES
追加するデータ値のリストを 1 つ以上指定します。column_list (指定されている場合) またはテーブル内の各列ごとに 1 つのデータ値が必要です。値リストは、かっこで囲む必要があります。値リスト内の値がテーブル内の列と同じ順序で並んでいない場合、またはテーブル内のすべての列に対応していない場合、column_list を使用して、各入力値を格納する列を明示的に指定する必要があります。
Transact-SQL 行コンストラクター (テーブル値コンストラクターとも呼ばれる) を使用すると、単一の INSERT ステートメント内に複数の行を指定できます。行コンストラクターは、かっこで囲まれ、コンマで区切られた複数の値リストを含む単一の VALUES 句で構成されています。詳細については、「テーブル値コンストラクター (Transact-SQL)」を参照してください。
DEFAULT
データベース エンジンによって、列に対して定義されている既定値が読み込まれます。既定値がなく、列に対して NULL が許可されている場合は、NULL が挿入されます。timestamp 型が定義されている列には、次のタイムスタンプ値が挿入されます。DEFAULT は ID 列には有効ではありません。expression
定数、変数、または式を指定します。式には EXECUTE ステートメントを含めることができません。Unicode 文字データ型 nchar、nvarchar、および ntext を参照している場合は、"expression" の前に大文字の "N" を付ける必要があります。"N" を指定しないと、SQL Server では、文字列はデータベースまたは列の既定の照合順序に対応するコード ページに変換されます。このコード ページにない文字列は失われます。詳細については、「Unicode を使用したサーバー側のプログラミング」を参照してください。
derived_table
テーブルに読み込まれるデータの行を返す、有効な SELECT ステートメントを指定します。SELECT ステートメントには、共通テーブル式 (CTE) を含めることはできません。execute_statement
SELECT ステートメントまたは READTEXT ステートメントでデータを返す有効な EXECUTE ステートメントです。execute_statement を INSERT で使用する場合、各結果セットに、テーブルの列または column_list の列との互換性が必要です。
execute_statement を使用して、同じサーバー上またはリモート サーバー上で、ストアド プロシージャを実行できます。リモート サーバーのプロシージャが実行されると、結果セットがローカル サーバーに返され、ローカル サーバーのテーブルに読み込まれます。分散トランザクションでは、接続で複数のアクティブな結果セット (MARS) が有効になっている場合、execute_statement をループバック リンク サーバーに対して実行できません。
execute_statement が READTEXT ステートメントでデータを返す場合、各 READTEXT ステートメントは最大で 1 MB (1024 KB) のデータを返すことができます。また execute_statement は、拡張プロシージャで使用することもできます。execute_statement は、拡張プロシージャのメイン スレッドによって返されたデータを挿入します。ただし、メイン スレッド以外のスレッドからの出力は挿入しません。
テーブル値パラメーターは、INSERT EXEC ステートメントの対象として指定できませんが、INSERT EXEC 文字列またはストアド プロシージャにソースとして指定できます。詳細については、「テーブル値パラメーター (データベース エンジン)」を参照してください。
<dml_table_source>
INSERT、UPDATE、DELETE、または MERGE ステートメントの OUTPUT 句で返された行 (WHERE 句でフィルター処理される場合もあります) を対象のテーブルに挿入するように指定します。<dml_table_source> を指定する場合は、外部の INSERT ステートメントの対象が次の制限を満たしている必要があります。ビューではなくベース テーブルである必要があります。
リモート テーブルは使用できません。
トリガーが定義されているテーブルは使用できません。
主キー/外部キーのリレーションシップに加えることはできません。
マージ レプリケーションや、トランザクション レプリケーションの更新可能なサブスクリプションに加えることはできません。
データベース互換性レベルを 100 以上に設定する必要があります。詳細については、「OUTPUT 句 (Transact-SQL)」を参照してください。
<select_list>
Output 句から返された列のどれを挿入するかを指定するコンマ区切りのリストです。<select_list> 内の列は、値の挿入先である列と互換でなければなりません。<select_list> では、集計関数または TEXTPTR を参照できません。.gif "注意")
注SELECT リストに含まれる変数は、<dml_statement_with_output_clause> で加えられる変更に関係なく、その元の値を参照します。
<dml_statement_with_output_clause>
影響を受ける行を OUTPUT 句で返す有効な INSERT、UPDATE、DELETE、または MERGE ステートメントです。このステートメントには WITH 句を指定できず、リモート テーブルまたはパーティション ビューを対象にすることもできません。UPDATE または DELETE を指定する場合、カーソルベースの UPDATE または DELETE は指定できません。ソース行を、入れ子になった DML ステートメントとして参照することはできません。WHERE <search_condition>
<dml_statement_with_output_clause> で返された行をフィルター処理する有効な <search_condition> を含んだ WHERE 句です。詳細については、「検索条件 (Transact-SQL)」を参照してください。このコンテキストで使用される <search_condition> には、サブクエリ、データにアクセスするスカラー ユーザー定義関数、集計関数、TEXTPTR、またはフルテキスト検索述語を含めることができません。DEFAULT VALUES
新しい行が、各列に対して定義されている既定値で構成されることを指定します。BULK
外部ツールでバイナリ データ ストリームをアップロードする際に使用されます。このオプションは、SQL Server Management Studio、SQLCMD、OSQL などのツールや、SQL Server Native Client などのデータ アクセス アプリケーション プログラミング インターフェイスで使用することは想定されていません。FIRE_TRIGGERS
バイナリ データ ストリームのアップロード処理中に、インポート先のテーブルで定義されている挿入トリガーを実行します。詳細については、「BULK INSERT (Transact-SQL)」を参照してください。CHECK_CONSTRAINTS
バイナリ データ ストリームのアップロード処理中に、対象テーブルまたはビューに対するすべての制約を検証します。詳細については、「BULK INSERT (Transact-SQL)」を参照してください。KEEPNULLS
バイナリ データ ストリームのアップロード処理中に、空の列の NULL 値を保持します。詳細については、「一括インポート中の NULL の保持または既定値の使用」を参照してください。KILOBYTES_PER_BATCH = kilobytes_per_batch
バッチあたりのデータの概算キロバイト数 (KB) を kilobytes_per_batch として指定します。詳細については、「BULK INSERT (Transact-SQL)」を参照してください。ROWS_PER_BATCH =rows_per_batch
バイナリ データ ストリーム内のデータ行の概算数を指定します。詳細については、「BULK INSERT (Transact-SQL)」を参照してください。注 列リストが指定されていない場合、構文エラーが発生します。
ベスト プラクティス
@@ROWCOUNT 関数を使用して、クライアントに挿入される行数を返します。詳細については、「@@ROWCOUNT (Transact-SQL)」を参照してください。
データの一括インポートに関するベスト プラクティス
INSERT INTO...SELECT を使用したデータ一括インポート時の最小ログ記録
INSERT INTO <対象テーブル> SELECT <列> FROM <ソース テーブル> を使用すると、最小ログ記録を行って、1 つのテーブル (ステージング テーブルなど) から別のテーブルに多数の行を効率的に転送できます。最小ログ記録を行うと、ステートメントのパフォーマンスが向上します。また、トランザクションの実行中に、使用可能なトランザクション ログ領域がこの操作でいっぱいになる可能性を低減します。
このステートメントの最小ログ記録には、次の要件があります。
データベース復旧モデルが単純復旧モデルまたは一括ログ復旧モデルに設定されている。
対象テーブルが、空のヒープか、空でないヒープである。
対象テーブルがレプリケーションで使用されない。
対象テーブルに TABLOCK ヒントが指定されている。
MERGE ステートメントでの挿入操作の結果としてヒープに挿入される行についても、最小ログ記録が行われる場合があります。
より制限の少ない一括更新ロックを保持する BULK INSERT ステートメントとは異なり、TABLOCK ヒントが指定された INSERT INTO...SELECT は、テーブルに対する排他的な (X) ロックを保持します。したがって、並列挿入操作を使用して行を挿入することはできません。ロックの詳細については、「ロック モード」を参照してください。
OPENROWSET および BULK によるデータの一括インポート
OPENROWSET 関数では次のテーブル ヒントを使用できます。これらのテーブル ヒントにより、一括読み込みの最適化を INSERT ステートメントで利用できます。
TABLOCK ヒントを使用すると、挿入操作のログ レコード数を最小化できます。データベース復旧モデルが単純復旧モデルまたは一括ログ復旧モデルに設定されている必要があります。また、対象テーブルはレプリケーションで使用できません。詳細については、「一括インポートで最小ログ記録を行うための前提条件」を参照してください。
IGNORE_CONSTRAINTS ヒントを使用すると、FOREIGN KEY および CHECK の制約チェックを一時的に無効にできます。
IGNORE_TRIGGERS ヒントを使用すると、トリガーの実行を一時的に無効にできます。
KEEPDEFAULTS ヒントを使用すると、データ レコードにテーブルの列値が含まれていない場合に、NULL の代わりにテーブル列の既定値を挿入できます。
KEEPIDENTITY ヒントを使用すると、インポートしたデータ ファイルの ID 値を対象テーブルの ID 列に使用できます。
これらは、BULK INSERT コマンドで使用可能な最適化と似ています。詳細については、「テーブル ヒント (Transact-SQL)」を参照してください。
データ型
行の挿入時には、次のデータ型の動作を考慮してください。
char、varchar、または varbinary 型の列に値が読み込まれる場合、後続の空白 (char と varchar の場合は空白、varbinary の場合は 0) の埋め込みや切り捨ては、テーブルが作成されたときに列に対して定義された SET ANSI_PADDING の設定値によって決まります。詳細については、「SET ANSI_PADDING (Transact-SQL)」を参照してください。
次の表は、SET ANSI_PADDING OFF の既定の操作を示します。
データ型
既定の操作
char
定義された列幅になるように値に空白を埋めます。
varchar
空白以外の最終文字に達するまで、後続の空白を削除します。文字列が空白だけで構成されている場合は、1 つのスペース文字を残して、後続の空白を削除します。
varbinary
後続の 0 を削除します。
varchar 型または text 型の列に空文字列 (' ') が読み込まれると、既定の操作では、長さが 0 の文字列が読み込まれます。
text 型列または image 型列に NULL 値を挿入した場合、有効なテキスト ポインターは作成されず、また、8 KB のテキスト ページもあらかじめ割り当てられません。text データおよび image データの挿入の詳細については、「text 型、ntext 型、image 型の関数の使用」を参照してください。
uniqueidentifier 型で作成される列は、特別にフォーマットされた 16 バイトのバイナリ値を格納します。ID 列の場合とは異なり、データベース エンジンは、uniqueidentifier 型の列に対して自動的に値を生成しません。挿入操作の際、uniqueidentifier 列には、uniqueidentifier 型の変数と、xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (ハイフンを含む 36 文字で、x は 0 ～ 9 または a ～ f の範囲の 16 進数値) という形式の文字列定数を使用できます。たとえば、uniqueidentifier 変数または列には、6F9619FF-8B86-D011-B42D-00C04FC964FF という値を指定できます。GUID を取得するには、NEWID() 関数を使用します。
ユーザー定義型の列への値の挿入
ユーザー定義型の列に値を挿入するには、次のようにします。
そのユーザー定義型の値を指定します。
ユーザー定義型で SQL Server システム データ型からの暗黙的または明示的な変換がサポートされている場合は、そのシステム データ型の値を指定します。次の例は、文字列からの明示的な変換によって、ユーザー定義型 Point の列に値を挿入する方法を示しています。
INSERT INTO Cities (Location) VALUES ( CONVERT(Point, '12.3:46.2') );明示的な変換を実行することなく、バイナリ値を指定することもできます。これは、すべてのユーザー定義型が、バイナリからの暗黙的な変換が可能であるためです。変換とユーザー定義型の詳細については、「ユーザー定義型に対する操作」を参照してください。
そのユーザー定義型の値を返すユーザー定義関数を呼び出します。次の例では、ユーザー定義関数 CreateNewPoint() を使用してユーザー定義型 Point の新しい値を作成し、この値を Cities テーブルに挿入します。
INSERT INTO Cities (Location) VALUES ( dbo.CreateNewPoint(x, y) );
エラー処理
TRY…CATCH 構造でステートメントを指定することで、INSERT ステートメントのエラー処理を実装できます。詳細については、「Transact-SQL での TRY...CATCH の使用」を参照してください。
INSERT ステートメントが制約やルールに違反していたり、その値が列のデータ型と互換性を持たない場合は、ステートメントが失敗し、エラー メッセージが返されます。
INSERT が SELECT または EXECUTE で複数の行を読み込んでいる場合、読み込まれている値でルールや制約の違反が発生すると、ステートメントが停止し、行は読み込まれません。
INSERT ステートメントの中で、式の評価中に算術エラー (オーバーフロー、0 による除算、またはドメイン エラー) が発生すると、データベース エンジンでは、SET ARITHABORT が ON に設定されている場合と同様に、これらのエラーが処理されます。バッチは停止し、エラー メッセージが返されます。SET ARITHABORT と SET ANSI_WARNINGS を OFF に設定して式を評価中に、INSERT、DELETE、または UPDATE ステートメントで算術演算エラー、オーバーフロー、0 除算、またはドメイン エラーが検出されると、SQL Server では NULL 値が挿入または更新されます。出力先の列で NULL 値が許容されない場合は、挿入または更新処理は失敗し、エラーが返されます。詳細については、「ARITHABORT と ARITHIGNORE が ON に設定されたときの動作」を参照してください。
相互運用性
テーブルやビューを対象とする INSERT 操作で INSTEAD OF トリガーが定義されている場合は、INSERT ステートメントの代わりにトリガーが実行されます。INSTEAD OF トリガーの詳細については、「CREATE TRIGGER (Transact-SQL)」を参照してください。
制限事項と制約事項
リモート テーブルに値を挿入するとき、すべての列のすべての値が指定されている場合を除いて、指定された値をどの列に挿入するかをユーザーが指定する必要があります。
SET ROWCOUNT オプションの設定は、ローカルおよびリモート パーティション ビューに対する INSERT ステートメントにおいて無視されます。また、リモート テーブルに対する INSERT ステートメントでは、このオプションはサポートされません。
| 重要 |
|---|
SQL Server の将来のリリースでは、SET ROWCOUNT を使用しても、DELETE、INSERT、および UPDATE ステートメントが影響を受けることはありません。新しい開発作業では DELETE、INSERT、および UPDATE ステートメントでの SET ROWCOUNT の使用を避け、現在 SET ROWCOUNT を使用しているアプリケーションは変更を検討してください。代わりに TOP 句を使用することをお勧めします。 |
ロック動作
INSERT ステートメントでは、常に、そのステートメントで変更するテーブルについて排他 (X) ロックを獲得し、トランザクションが完了するまでそのロックを保持します。排他 (X) ロックをかけた以外のトランザクションはデータを変更できませんが、NOLOCK ヒントまたは READ UNCOMMITTED 分離レベルが指定されている場合に限り、読み取り操作は行うことができます。詳細については、「データベース エンジンのロック」を参照してください。
ログ記録の動作
INSERT ステートメントでは、BULK キーワードを指定して OPENROWSET 関数を使用している場合や、INSERT INTO <対象テーブル> SELECT <列> FROM <ソース テーブル> を使用している場合を除いて、常にすべてのログが記録されます。これらの操作のログへの記録は最小限にできます。詳細については、前の「データの一括読み込みの推奨事項」を参照してください。
セキュリティ
リンク サーバーに接続する場合、送信側サーバーは受信側サーバーに接続するためにログイン名とパスワードをリンク サーバーに代わって提供します。この接続を機能させるには、sp_addlinkedsrvlogin を使用して、リンク サーバー間でログイン マッピングを作成する必要があります。詳細については、「リンク サーバーのセキュリティ」を参照してください。
OPENROWSET(BULK…) を使用するにあたっては、SQL Server で権限借用がどのように処理されるかを理解しておくことが重要です。詳細については、「BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート」の「セキュリティの注意点」を参照してください。
権限
対象のテーブルに対する INSERT 権限が必要です。
INSERT 権限は、既定では sysadmin 固定サーバー ロール、db_owner および db_datawriter 固定データベース ロールのメンバー、およびテーブル所有者に与えられています。sysadmin、db_owner、および db_securityadmin ロールのメンバー、およびテーブル所有者は、他のユーザーに権限を譲渡できます。
OPENROWSET 関数の BULK オプションで INSERT を実行するには、sysadmin 固定サーバー ロールまたは bulkadmin 固定サーバー ロールのメンバーであることが必要です。