使用 INSERT 和 VALUES 插入行

INSERT 語(yǔ)句可向表中添加一個(gè)或多個(gè)新行。在簡(jiǎn)化處理中，INSERT 具有以下格式：

INSERT [INTO] table_or_view [(column_list)] VALUES(data_values)

• column_list 是列名的列表，列名以逗號(hào)分隔，用于指定為其提供數(shù)據(jù)的列。如果未指定 column_list，表或視圖中的所有列都將接收到數(shù)據(jù)。當(dāng) column_list 未指定表或視圖中的所有列時(shí)，系統(tǒng)會(huì)將默認(rèn)值（如果為列定義了默認(rèn)值）或 NULL 插入未在列表中指定的任一列。未在列列表中指定的所有列必須允許空值或分配了默認(rèn)值。
• VALUES 關(guān)鍵字為表的某一行或多個(gè)行指定值。這些值指定為逗號(hào)分隔的標(biāo)量表達(dá)式列表，表達(dá)式的數(shù)據(jù)類(lèi)型、精度和小數(shù)位數(shù)必須與列的列表中對(duì)應(yīng)列一致，或者可以隱式轉(zhuǎn)換為列的列表中對(duì)應(yīng)列。

INSERT 語(yǔ)句不指定下列類(lèi)型列的值，因?yàn)?SQL Server 數(shù)據(jù)庫(kù)引擎將為這些列生成值：

• 具有 timestamp 數(shù)據(jù)類(lèi)型。使用當(dāng)前的時(shí)間戳值。
• 具有默認(rèn)值的列，此默認(rèn)值用 NEWID 函數(shù)生成唯一的 GUID 值。
• 可以為 Null。使用 Null 值。
• 標(biāo)識(shí)列。標(biāo)識(shí)列具有 IDENTITY 屬性的列，此屬性為該列生成值。
• 計(jì)算列。計(jì)算列是指定義為通過(guò) CREATE TABLE 語(yǔ)句中一個(gè)或多個(gè)其他列計(jì)算的表達(dá)式的虛擬列，例如：

CREATE TABLE TestTable
  (ColA INT PRIMARY KEY,
   ColB INT NOT NULL,
   ColC AS (ColA + ColB) * 2);

鎖定行為
??INSERT 語(yǔ)句總是在其修改的表上獲取排他 (X) 鎖并在事務(wù)完成之前持有該鎖。使用排他鎖（X 鎖）時(shí)，任何其他事務(wù)都無(wú)法修改數(shù)據(jù)；僅在使用 NOLOCK 提示或未提交讀隔離級(jí)別時(shí)才會(huì)進(jìn)行讀取操作。

日志記錄行為
??INSERT 語(yǔ)句始終完全記入日志，只有在將 OPENROWSET 函數(shù)與 BULK 關(guān)鍵字一起使用或者在使用 INSERT INTO <target_table> SELECT <columns> FROM <source_table> 時(shí)除外。這些操作可進(jìn)行最小日志記錄。

【示例】
??以下示例顯示了如何將行插入包含自動(dòng)生成值或具有默認(rèn)值的列的表中。INSERT 語(yǔ)句插入一些行，這些行只有部分列包含值。在最后一個(gè) INSERT 語(yǔ)句中，未指定列并只插入了默認(rèn)值。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.T1', 'U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1 
(
    column_1 AS 'Computed column ' + column_2, 
    column_2 varchar(30) 
        CONSTRAINT default_name DEFAULT ('my column default'),
    column_3 rowversion,
    column_4 varchar(40) NULL
);
GO
INSERT INTO dbo.T1 (column_4) 
    VALUES ('Explicit value');
INSERT INTO dbo.T1 (column_2, column_4) 
    VALUES ('Explicit value', 'Explicit value');
INSERT INTO dbo.T1 (column_2) 
    VALUES ('Explicit value');
INSERT INTO T1 DEFAULT VALUES; 
GO
SELECT column_1, column_2, column_3, column_4
FROM dbo.T1;
GO

查詢(xún)結(jié)果如下：

column_1                                       column_2                       column_3   column_4
---------------------------------------------- ------------------------------ ---------- --------------------
Computed column my column default              my column default              0x00000000 Explicit value
Computed column Explicit value                 Explicit value                 0x00000000 Explicit value
Computed column Explicit value                 Explicit value                 0x00000000 NULL
Computed column my column default              my column default              0x00000000 NULL

使用 INSERT 和 SELECT 子查詢(xún)插入行

INSERT 語(yǔ)句中的 SELECT 子查詢(xún)可用于將一個(gè)或多個(gè)表或視圖中的值添加到另一個(gè)表中。使用 SELECT 子查詢(xún)還可以同時(shí)插入多行。
??子查詢(xún)的選擇列表必須與 INSERT 語(yǔ)句的列列表匹配。如果沒(méi)有指定列列表，選擇列表必須與正在其中執(zhí)行插入操作的表或視圖的列匹配。
??在以下示例中，INSERT 語(yǔ)句將Sales.SalesReason 表中 SalesReason 為 Marketing 的所有行中的一些數(shù)據(jù)插入到一個(gè)單獨(dú)的表中：

USE AdventureWorks2008R2;
GO
CREATE TABLE MySalesReason (
    SalesReasonID int NOT NULL,
    Name nvarchar(50),
    ModifiedDate datetime);
GO
INSERT INTO MySalesReason
    SELECT SalesReasonID, Name, ModifiedDate
    FROM AdventureWorks2008R2.Sales.SalesReason
    WHERE ReasonType = N'Marketing';
GO

使用 SELECT INTO 插入行

SELECT INTO 語(yǔ)句用于創(chuàng)建一個(gè)新表，并用 SELECT 語(yǔ)句的結(jié)果集填充該表。SELECT INTO 可將幾個(gè)表或視圖中的數(shù)據(jù)組合成一個(gè)表。也可用于創(chuàng)建一個(gè)包含選自鏈接服務(wù)器的數(shù)據(jù)的新表。
??新表的結(jié)構(gòu)由選擇列表中表達(dá)式的屬性定義。下面的示例中，從多個(gè)雇員和與地址相關(guān)的表中選擇七列來(lái)創(chuàng)建表 dbo.EmployeeAddresses。

USE AdventureWorks2008R2;
GO
SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City, 
    sp.Name AS [State/Province], a.PostalCode
INTO dbo.EmployeeAddresses
FROM Person.Person AS c
    JOIN HumanResources.Employee AS e 
    ON e.BusinessEntityID = c.BusinessEntityID
    JOIN Person.BusinessEntityAddress AS bea
    ON e.BusinessEntityID = bea.BusinessEntityID
    JOIN Person.Address AS a
    ON bea.AddressID = a.AddressID
    JOIN Person.StateProvince as sp 
    ON sp.StateProvinceID = a.StateProvinceID;
GO

SELECT INTO 不使用源表的分區(qū)方案。而新表是在默認(rèn)文件組中創(chuàng)建的，若要向已分區(qū)表插入行，首先必須創(chuàng)建已分區(qū)表，然后再使用 INSERT INTO…SELECT FROM 語(yǔ)句。
??使用 SELECT INTO 語(yǔ)句創(chuàng)建新表時(shí)，F(xiàn)ILESTREAM 屬性不傳輸。FILESTREAM BLOB 作為 varbinary(max) BLOB 復(fù)制并存儲(chǔ)在新表中。如果 FILESTREAM BLOB 超過(guò) 2 GB，則將引發(fā)以下錯(cuò)誤消息，并且語(yǔ)句停止：“正嘗試增長(zhǎng) LOB，使其超過(guò)允許的最大大小(2147483647 個(gè)字節(jié))”。

使用INSERT EXEC語(yǔ)句插入行

INSERT [INTO] table_name|table_variable [(column_list )] execute_statement

• table_name：表名，可以是永久表或臨時(shí)表
• table_variable：表變量(SQL Server不可用)
• execute_statement：任何有效的 EXECUTE 語(yǔ)句，它使用 SELECT 或 READTEXT 語(yǔ)句返回?cái)?shù)據(jù)。
• 如果 execute_statement 使用 INSERT，則每個(gè)結(jié)果集必須與表或 column_list 中的列兼容。
• 如果 execute_statement 使用 READTEXT 語(yǔ)句返回?cái)?shù)據(jù)，則每個(gè) READTEXT 語(yǔ)句最多可以返回 1 MB (1024 KB) 的數(shù)據(jù)。
• execute_statement 還可以用于擴(kuò)展過(guò)程。execute_statement 插入由擴(kuò)展過(guò)程的主線程返回的數(shù)據(jù)，但不插入主線程以外的線程的輸出。
• 不能將表值參數(shù)指定為 INSERT EXEC 語(yǔ)句的目標(biāo)；但是，可以將它指定為 INSERT EXEC 字符串或存儲(chǔ)過(guò)程中的源。
• 不能在 INSERT...EXEC 語(yǔ)句中使用 OUTPUT 子句

使用 TOP 限制插入的行

可以使用 TOP 關(guān)鍵字限制插入的行數(shù)。在與 INSERT語(yǔ)句結(jié)合使用的 TOP 表達(dá)式中引用的行不按任何順序排列。TOP(n) 隨機(jī)返回 n 行。
??例如，下面的 INSERT 語(yǔ)句包含 ORDER BY 子句，但該子句并不影響由 INSERT 語(yǔ)句直接引用的行，INSERT 語(yǔ)句選擇 SELECT 語(yǔ)句返回的任意兩行

INSERT TOP (2) INTO Table2 (ColumnB) 
    SELECT ColumnA FROM Table1 
    ORDER BY ColumnA;

若要確保插入 SELECT 子查詢(xún)返回的前兩行，請(qǐng)按如下所示重寫(xiě)該查詢(xún)。

INSERT INTO Table2 (ColumnB) 
    SELECT TOP (2) ColumnA FROM Table1 
    ORDER BY ColumnA;

INSERT (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 
        }
    }
}
[; ]

<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 ] ) ]

• TOP (expression) [ PERCENT ]
  指定將插入的隨機(jī)行的數(shù)目或百分比。expression 可以是行數(shù)或行的百分比。
• INTO：一個(gè)可選的關(guān)鍵字，可以將它用在 INSERT 和目標(biāo)表之間。
• server_name：表或視圖所在的鏈接服務(wù)器的名稱(chēng)。
• database_name：數(shù)據(jù)庫(kù)的名稱(chēng)。
• schema_name：該表或視圖所屬架構(gòu)的名稱(chēng)。
• table_or view_name：要接收數(shù)據(jù)的表或視圖的名稱(chēng)。表變量在其作用域內(nèi)可用作 INSERT 語(yǔ)句中的表源。
• OUTPUT 子句：將插入行作為插入操作的一部分返回。結(jié)果可返回到處理應(yīng)用程序或插入到表或表變量中以供進(jìn)一步處理。
  ??引用本地分區(qū)視圖、分布式分區(qū)視圖或遠(yuǎn)程表的 DML 語(yǔ)句或包含 execute_statement 的 INSERT 語(yǔ)句都不支持OUTPUT 子句。在包含 <dml_table_source> 子句的 INSERT 語(yǔ)句中不支持 OUTPUT INTO 子句。
• VALUES
  引入要插入的數(shù)據(jù)值的一個(gè)或多個(gè)列表。對(duì)于 column_list（如果已指定）或表中的每個(gè)列，都必須有一個(gè)數(shù)據(jù)值。必須用圓括號(hào)將值列表括起來(lái)。
• DEFAULT
  強(qiáng)制數(shù)據(jù)庫(kù)引擎加載為列定義的默認(rèn)值。如果某列并不存在默認(rèn)值，并且該列允許 Null 值，則插入 NULL。對(duì)于使用 timestamp 數(shù)據(jù)類(lèi)型定義的列，插入下一個(gè)時(shí)間戳值。DEFAULT 對(duì)標(biāo)識(shí)列無(wú)效。
• expression：一個(gè)常量、變量或表達(dá)式。表達(dá)式不能包含 EXECUTE 語(yǔ)句。
• <dml_table_source>
  ??指定插入目標(biāo)表的行是 INSERT、UPDATE、DELETE 或 MERGE 語(yǔ)句的OUTPUT 子句返回的行(和上面的<OUTPUT Clause>沒(méi)啥關(guān)系，這里是使用DML產(chǎn)生所要插入目標(biāo)表的行，它們是使用OUTPUT返回的)。
  ??如果指定了 <dml_table_source>，外部 INSERT 語(yǔ)句的目標(biāo)必須滿(mǎn)足以下限制：
• 必須是基表而不是視圖。
• 不能是遠(yuǎn)程表。
• 不能對(duì)其定義任何觸發(fā)器。
• 不能參與任何主鍵-外鍵關(guān)系。
• 不能參與合并復(fù)制或事務(wù)復(fù)制的可更新訂閱。
• <select_list>
  指定要插入 OUTPUT 子句所返回的列的逗號(hào)分隔列表。<select_list> 中的列必須與要插入值的列兼容。<select_list> 無(wú)法引用聚合函數(shù)或 TEXTPTR。
• <dml_statement_with_output_clause>
  ??在 OUTPUT 子句中返回受影響行的有效 INSERT、UPDATE、DELETE 或 MERGE 語(yǔ)句(和上面的<OUTPUT Clause>沒(méi)啥關(guān)系，這是產(chǎn)生data_table_source的DML語(yǔ)句)。
  ??語(yǔ)句中不能包含 WITH 子句，且不能以遠(yuǎn)程表或分區(qū)視圖為目標(biāo)。如果指定了 UPDATE 或 DELETE，則所指定的 UPDATE 或 DELETE 不能是基于游標(biāo)的。源行不能作為嵌套的 DML 語(yǔ)句進(jìn)行引用。
• WHERE <search_condition>
  任意 WHERE 子句，其中包含對(duì) <dml_statement_with_output_clause> 返回的行進(jìn)行篩選的有效 <search_condition>

【示例】

A. 將 OUTPUT INTO 用于簡(jiǎn)單 INSERT 語(yǔ)句
??下例向 ScrapReason 表插入一行，并使用 OUTPUT 子句將語(yǔ)句的結(jié)果返回給@MyTableVartable變量。由于 ScrapReasonID 列使用 IDENTITY 屬性定義，因此未在 INSERT 語(yǔ)句中為該列指定一個(gè)值。但請(qǐng)注意，將在列inserted.ScrapReasonID內(nèi)的 OUTPUT 子句中返回由數(shù)據(jù)庫(kù)引擎為該列生成的值。

USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
                           Name varchar(50),
                           ModifiedDate datetime);
INSERT Production.ScrapReason
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
        INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason;
GO

**B、使用<dml_table_source>進(jìn)行插入 **

CREATE TABLE table1  
(  
    id INT,  
    employee VARCHAR(32)  
);  
CREATE TABLE table2
(  
    id INT,  
    person VARCHAR(32)  
);  

INSERT INTO table1 
SELECT a.id,a.person FROM (
   INSERT table2 
     OUTPUT INSERTED.id,INSERTED.person
   VALUES(1,'Ada')
) AS a
GO