• 發行項

INSERT (Transact-SQL)

在 SQL Server 2008 R2 的資料表或檢視表中加入一個或多個新資料列。如需範例，請參閱＜INSERT 範例 (Transact-SQL)＞。

主題連結圖示Transact-SQL 語法慣例

語法

-- 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 運算式所參考的資料列並不依照任何順序來排列。

    TOP 中用於分隔 expression 的括號，在 INSERT、UPDATE 和 DELETE 陳述式中是必要的。如需詳細資訊，請參閱＜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
    這是將接收資料之資料表或檢視表的名稱。

    資料表變數在它本身的範圍內，可在 INSERT 陳述式中當做資料表來源使用。

    table_or_view_name 所參考的檢視表必須能夠更新，而且必須只參考該檢視表 FROM 子句中的單一基底資料表。例如，指向多資料表檢視表的 INSERT，必須使用只參考單一基底資料表之各個資料行的 column_list。如需有關可更新檢視表的詳細資訊，請參閱＜CREATE VIEW (Transact-SQL)＞。

  • rowset_function_limited
    這是 OPENQUERY 或 OPENROWSET 函數。這些函數的使用方式受限於存取遠端物件之 OLE DB 提供者的功能。如需詳細資訊，請參閱＜分散式查詢＞。

  • WITH ( <table_hint_limited> [...n ] )
    指定目標資料表允許使用的一個或多個資料表提示。WITH 關鍵字和括號都是必要的。

    不允許使用 READPAST、NOLOCK 和 READUNCOMMITTED。如需有關資料表提示的詳細資訊，請參閱＜資料表提示 (Transact-SQL)＞。

    重要事項重要事項

    SQL Server 未來的版本將移除指定 INSERT 陳述式的目標資料表之 HOLDLOCK、SERIALIZABLE、READCOMMITTED、REPEATABLEREAD 或 UPDLOCK 提示的功能。這些提示不會影響 INSERT 陳述式的效能。請避免在新的開發工作中使用它們，並規劃修改目前在使用它們的應用程式。

    指定 INSERT 陳述式目標資料表之 TABLOCK 提示的效果，與指定 TABLOCKX 提示相同。獨佔鎖定是在資料表上取得的。

  • (column_list)
    這是要插入資料的一個或多個資料行所組成的清單。column_list 必須括在括號中，而且是以逗號分隔。

    如果資料行不在 column_list 中，Database Engine 必須能夠提供以資料行定義為基礎的值；否則，便無法載入這個資料列。如果資料行符合下列條件，Database Engine 會自動提供資料行值：

    • 具有 IDENTITY 屬性。使用下一個累加識別值。

    • 有預設值。使用資料行的預設值。

    • 有 timestamp 資料類型。使用目前的時間戳記值。

    • 可為 Null。使用 Null 值。

    • 這是計算資料行。使用計算的值。

    當您將明確的值插入識別欄位時，必須使用 column_list 和值清單，而且資料表的 SET IDENTITY_INSERT 選項必須是 ON。

  • OUTPUT 子句
    在插入作業中，傳回插入的資料列。這些結果可以傳回給處理應用程式或插入資料表或資料表變數，以便進一步處理。

    在參考本機資料分割檢視表、分散式資料分割檢視表或遠端資料表的 DML 陳述式中，以及在包含 execute_statement 的 INSERT 陳述式中，都不支援 OUTPUT 子句。含有 <dml_table_source> 子句的 INSERT 陳述式不支援 OUTPUT INTO 子句。

  • VALUES
    導入要插入的資料值清單。column_list (如果有指定) 或資料表中的每個資料行，都必須有一個資料值。這份值清單必須括在括號中。

    如果值清單中的值與資料表中的資料行順序不同，或每個資料表資料行並未各有一個值，就必須利用 column_list 來明確指定儲存每個內送值的資料行。

    您可以使用 Transact-SQL 資料列建構函式 (也稱為資料表值建構函式)，在單一 INSERT 陳述式中指定多個資料列。資料列建構函式是由單一 VALUES 子句所組成，其中包含括號所括住的多值清單，而且會以逗號分隔。如需詳細資訊，請參閱＜資料表值建構函式 (Transact-SQL)＞。

  • DEFAULT
    強制 Database Engine 載入定義給資料行的預設值。如果資料行的預設值不存在，而且資料行允許 Null 值，就會插入 NULL。如果是用 timestamp 資料類型來定義的資料行，就會插入下一個時間戳記值。識別欄位的 DEFAULT 無效。

  • 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 可用於在相同伺服器或遠端伺服器中執行預存程序。執行遠端伺服器中的程序，而且結果集會傳回本機伺服器，且載入本機伺服器的資料表中。在分散式交易中，如果連接啟用了 Multiple Active Result Sets (MARS)，就無法對回送連結的伺服器發出 execute_statement。

    如果 execute_statement 是隨著 READTEXT 陳述式而傳回資料，則每個 READTEXT 陳述式最多可以傳回 1 MB (1024 KB) 的資料。execute_statement 也可用於搭配擴充程序。execute_statement 會插入擴充程序主要執行緒所傳回的資料；不過，不會插入主要執行緒以外之執行緒的輸出。

    您無法將資料表值參數指定為 INSERT EXEC 陳述式的目標。不過，您可以在 INSERT EXEC 字串或預存程序中，將它指定為來源。如需詳細資訊，請參閱＜資料表值參數 (Database Engine)＞。

  • <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。

    [!附註]

    SELECT 清單中所列的任何變數都會參考其原始值，不論在 <dml_statement_with_output_clause> 中對它們做的變更為何。

  • <dml_statement_with_output_clause>
    這是有效的 INSERT、UPDATE、DELETE 或 MERGE 陳述式，可在 OUTPUT 子句中傳回受影響的資料列。此陳述式不能包含 WITH 子句，也不能以遠端資料表或資料分割檢視表為目標。如果指定了 UPDATE 或 DELETE，它不能是以資料指標為基礎的 UPDATE 或 DELETE。來源資料列不能當做巢狀 DML 陳述式來參考。

  • WHERE <search_condition>
    這是任何 WHERE 子句，其中包含可篩選 <dml_statement_with_output_clause> 傳回之資料列的有效 <search_condition>。如需詳細資訊，請參閱＜搜尋條件 (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
    以 kilobytes_per_batch 指定每一批資料的近似 KB 數。如需詳細資訊，請參閱＜BULK INSERT (Transact-SQL)＞。

  • ROWS_PER_BATCH =rows_per_batch
    指出二進位資料流中大約的資料列數。如需詳細資訊，請參閱＜BULK INSERT (Transact-SQL)＞。

    注意：如果沒有提供資料行清單，可能會引發語法錯誤。

最佳作法

您可以使用 @@ROWCOUNT 函數，將插入的資料列數目傳回給用戶端。如需詳細資訊，請參閱＜@@ROWCOUNT (Transact-SQL)＞。

大量匯入資料的最佳作法

使用 INSERT INTO…SELECT 搭配最低限度記錄來大量匯入資料

您可以使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 搭配最低限度記錄，有效率地將大量資料列從某份資料表 (例如臨時資料表) 傳送至另一份資料表。最低限度記錄可以改善此陳述式的效能並且降低交易期間作業填滿可用交易記錄空間的可能性。

此陳述式的最低限度記錄具有下列需求：

  • 資料庫的復原模式設為簡單或大量記錄。

  • 目標資料表是空白或非空白的堆積。

  • 目標資料表未用於複寫。

  • 針對目標資料表指定了 TABLOCK 提示。

由於 MERGE 陳述式中的插入動作而插入堆積的資料列也可以採用最低限度記錄。

與 BULK INSERT 陳述式 (保存限制較低的大量更新鎖定) 不同之處在於，含有 TABLOCK 提示的 INSERT INTO…SELECT 會保存資料表的獨佔 (X) 鎖定。這代表您無法使用平行插入作業插入資料列。如需有關鎖定的詳細資訊，請參閱＜鎖定模式＞。

利用 OPENROWSET 和 BULK 來大量匯入資料

OPENROWSET 函數可接受下列資料表提示，這些提示會針對大量載入最佳化提供 INSERT 陳述式：

  • TABLOCK 提示可以將插入作業的記錄數目減至最小。資料庫的復原模式必須設定為簡單或大量記錄，而且目標資料表不得用於複寫。如需詳細資訊，請參閱＜大量匯入採用最低限度記錄的必要條件＞。

  • IGNORE_CONSTRAINTS 提示可以暫時停用 FOREIGN KEY 和 CHECK 條件約束檢查。

  • IGNORE_TRIGGERS 提示可以暫時停用觸發程序執行。

  • KEEPDEFAULTS 提示允許在資料記錄缺少資料行的值時，當資料表資料行有預設值便插入這個預設值，而不是 NULL。

  • KEEPIDENTITY 提示允許將匯入之資料檔中的識別值用於目標資料表的識別欄位。

這些最佳化類似於 BULK INSERT 命令所能使用的最佳化。如需詳細資訊，請參閱＜資料表提示 (Transact-SQL)＞。

資料類型

當您插入資料列時，請考量以下資料類型行為：

  • 如果正在將值載入 char、varchar 或 varbinary 資料類型的資料行，填補或截斷尾端空白 (char 和 varchar 是空格， varbinary 是零) 取決於建立資料表時定義給資料行的 SET ANSI_PADDING 設定。如需詳細資訊，請參閱＜SET ANSI_PADDING (Transact-SQL)＞。

    下表顯示 SET ANSI_PADDING OFF 的預設作業。

    資料類型

    預設作業

    char

    值填補空格到定義的資料行寬度。

    varchar

    移除尾端空格到最後一個非空格字元，或到只由空格組成之字串的一個空格字元。

    varbinary

    移除尾端零。

  • 如果將空字串 (' ') 載入 varchar 或 text 資料類型的資料行，預設作業便是載入長度為零的字串。

  • 將 Null 值插入 text 或 image 資料行不會建立有效的文字指標，也不會預先配置 8KB 文字頁面。如需有關插入 text 和 image 資料的詳細資訊，請參閱＜使用 text、ntext 與 image 函數＞。

  • 利用 uniqueidentifier 資料類型來建立的資料行會儲存特殊格式的 16 位元組二進位值。這不像識別欄位，Database Engine 不會自動產生 uniqueidentifier 資料類型的資料行值。在插入作業期間，uniqueidentifier 資料行可以使用 uniqueidentifier 資料類型的變數及 xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx 格式的字串常數 (36 個字元，包括連字號，其中 x 是 0-9 或 a-f 範圍內的十六進位數字)。例如，6F9619FF-8B86-D011-B42D-00C04FC964FF 便是 uniqueidentifier 變數或資料行的有效值。請利用 NEWID() 函數來取得全域唯一識別碼 (GUID)。

將值插入使用者定義型別資料行

您可以利用下列方式，在使用者定義型別資料行中插入值：

  • 提供使用者定義型別的值。

  • 只要使用者定義型別支援從這個類型進行隱含或明確的轉換，便在 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 陳述式在運算式評估期間發生算術錯誤 (溢位、除以零或範圍錯誤) 時，Database Engine 會依照 SET ARITHABORT 設為 ON 的方式來處理這些錯誤。此時，批次會停止運作，而且系統會傳回錯誤訊息。在運算式評估期間，當 SET ARITHABORT 和 SET ANSI_WARNINGS 是 OFF 時，如果 INSERT、DELETE 或 UPDATE 陳述式發現算術錯誤、溢位、除以零或範圍錯誤，SQL Server 會插入或更新 NULL 值。如果目的地資料行不可設為 Null，插入或更新動作就會失敗，而且使用者會收到錯誤。如需詳細資訊，請參閱＜ARITHABORT 及 ARITHIGNORE 設為 ON 時的行為＞。

互通性

定義資料表或檢視表之 INSERT 動作的 INSTEAD OF 觸發程序時，系統會執行觸發程序，而不是 INSERT 陳述式。如需有關 INSTEAD OF 觸發程序的詳細資訊，請參閱＜CREATE TRIGGER (Transact-SQL)＞。

限制事項

當您將值插入遠端資料表時，如果並未完整指定所有資料行的所有值，您必須識別要插入指定值的資料行。

針對本機和遠端資料分割檢視表來進行的 INSERT 陳述式，其 SET ROWCOUNT 選項的設定會被忽略。此外，針對遠端資料表發出的 INSERT 陳述式不支援這個選項。

重要事項重要事項

使用 SET ROWCOUNT 並不會影響 SQL Server 未來版本的 DELETE、INSERT 和 UPDATE 陳述式。請勿在新的開發工作中使用 SET ROWCOUNT 搭配 DELETE、INSERT 和 UPDATE 陳述式，並請規劃修改目前正在使用它的應用程式。我們建議您改用 TOP 子句。

鎖定行為

INSERT 陳述式永遠都會取得它所修改之資料表的獨佔 (X) 鎖定，並保留該鎖定直到交易完成為止。運用獨佔 (X) 鎖定，沒有其他交易可修改資料；只有使用 NOLOCK 提示或讀取未認可隔離等級，才能進行讀取作業。如需詳細資訊，請參閱＜Database Engine 中的鎖定＞。

記錄行為

除了搭配 BULK 關鍵字使用 OPENROWSET 函數或是使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 以外，INSERT 陳述式一定會完整記錄。這些作業可以進行最低限度記錄。如需詳細資訊，請參閱本主題前面的＜大量載入資料的最佳作法＞一節。

安全性

在連結的伺服器連接期間，傳送端伺服器會提供一個登入名稱與密碼來代表它本身，以連接到接收端伺服器。若要讓這個連接運作，您必須使用 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 固定伺服器角色的成員。

範例

如需範例，請參閱＜INSERT 範例 (Transact-SQL)＞。