SQL has a set of statements known as Data Manipulation Language (DML) that deals with data manipulation. SQL有一组称为数据操作语言（DML）的语句，用于处理数据操作。Some people think that DML involves only data modification, but it also involves data retrieval. 有些人认为DML不仅涉及数据修改，还涉及数据检索。DML includes the statements DML包括SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and MERGE. SELECT、INSERT、UPDATE、DELETE、TRUNCATE和MERGE语句。So far I've focused on the 到目前为止，我主要关注SELECT statement. SELECT语句。This chapter focuses on data-modification statements. 本章重点介绍数据修改语句。In addition to covering standard aspects of data modification, I'll also cover aspects specific to T-SQL.除了介绍数据修改的标准方面，我还将介绍特定于T-SQL的方面。
To avoid changing existing data in your sample database, most of the examples in this chapter create and populate new tables in the 为了避免更改示例数据库中的现有数据，本章中的大多数示例在dbo schema in the TSQLV4 database.TSQLV4数据库的dbo模式中创建并填充新表。
T-SQL provides several statements for inserting data into tables: T-SQL提供了几种将数据插入表的语句：INSERT VALUES, INSERT SELECT, INSERT EXEC, SELECT INTO, and BULK INSERT. INSERT VALUES、INSERT SELECT、INSERT EXEC、SELECT INTO和BULK INSERT。I'll first describe those statements, and then I'll talk about tools for generating keys, such as the identity property and the sequence object.我将首先描述这些语句，然后讨论生成密钥的工具，例如标识属性和序列对象。
INSERT VALUES statementINSERT VALUES语句You use the standard 可以使用标准的INSERT VALUES statement to insert rows into a table based on specified values. INSERT VALUES语句根据指定的值将行插入表中。To practice using this statement and others, you will work with a table called 为了练习使用此语句和其他语句，您将在Orders in the dbo schema in the TSQLV4 database. TSQLV4数据库的dbo架构中使用名为Orders的表。Run the following code to create the 运行以下代码创建Orders table:Orders表：
USE TSQLV4;
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL
CONSTRAINT PK_Orders PRIMARY KEY,
orderdate DATE NOT NULL
CONSTRAINT DFT_orderdate DEFAULT(SYSDATETIME()),
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL
);
The following example demonstrates how to use the 以下示例演示如何使用INSERT VALUES statement to insert a single row into the Orders table:INSERT VALUES语句将单行插入Orders表：
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
VALUES(10001, '20160212', 3, 'A');
Specifying the target column names right after the table name is optional, but by doing so, you control the value-column associations instead of relying on the order of the columns in the 在表名之后指定目标列名是可选的，但这样做可以控制值列关联，而不是依赖CREATE TABLE statement. CREATE TABLE语句中列的顺序。In T-SQL, specifying the 在T-SQL中，指定INTO clause is optional.INTO子句是可选的。
If you don't specify a value for a column, Microsoft SQL Server will use a default value if one was defined for the column. 如果未为列指定值，如果为该列定义了默认值，则Microsoft SQL Server将使用默认值。If a default value isn't defined and the column allows 如果未定义默认值，且该列允许NULLs, a NULL will be used. NULL值，则将使用NULL值。If no default is defined and the column does not allow 如果没有定义默认值，并且该列不允许NULLs and does not somehow get its value automatically, your INSERT statement will fail. NULL值，并且没有以某种方式自动获取其值，那么INSERT语句将失败。The following statement doesn't specify a value for the 下面的语句没有为orderdate column; rather, it relies on its default (SYSDATETIME):orderdate列指定值；相反，它依赖于其默认值（SYSDATETIME）：
INSERT INTO dbo.Orders(orderid, empid, custid)
VALUES(10002, 5, 'B');
T-SQL supports an enhanced standard T-SQL支持一个增强的标准VALUES clause you can use to specify multiple rows separated by commas. VALUES子句，您可以使用它来指定多个以逗号分隔的行。For example, the following statement inserts four rows into the 例如，以下语句在Orders table:Orders表中插入四行：
INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid)
VALUES
(10003, '20160213', 4, 'B'),
(10004, '20160214', 1, 'A'),
(10005, '20160213', 1, 'C'),
(10006, '20160215', 3, 'C');
This statement is processed as a transaction, meaning that if any row fails to enter the table, none of the rows in the statement enters the table.此语句作为事务处理，这意味着如果任何行未能进入表，则语句中的任何行都不会进入表。
There's more to this enhanced 这个增强的VALUES clause. VALUES子句还有更多内容。You can use it as a table-value constructor to construct a derived table. 可以将其用作表值构造函数来构造派生表。Here's an example:下面是一个例子：
SELECT *
FROM ( VALUES
(10003, '20160213', 4, 'B'),
(10004, '20160214', 1, 'A'),
(10005, '20160213', 1, 'C'),
(10006, '20160215', 3, 'C') )
AS O(orderid, orderdate, empid, custid);
Following the parentheses that contain the table value constructor, you assign an alias to the table (在包含表值构造函数的括号之后，为表分配别名（本例中为O in this case), and following the table alias, you assign aliases to the target columns in parentheses. O），在表别名之后，为括号中的目标列分配别名。This query generates the following output:此查询生成以下输出：
orderid orderdate empid custid
----------- ----------- ----------- ------
10003 20160213 4 B
10004 20160214 1 A
10005 20160213 1 C
10006 20160215 3 C
INSERT SELECT statementINSERT SELECT语句The standard 标准INSERT SELECT statement inserts a set of rows returned by a SELECT query into a target table. INSERT SELECT语句将SELECT查询返回的一组行插入到目标表中。The syntax is similar to that of an 该语法与INSERT VALUES statement, but instead of using the VALUES clause, you specify a SELECT query. INSERT VALUES语句的语法类似，但不是使用VALUES子句，而是指定SELECT查询。For example, the following code inserts into the 例如，以下代码将针对dbo.Orders table the result of a query against the Sales.Orders table and returns orders that were shipped to the United Kingdom:Sales.Orders表的查询结果插入dbo.Orders表，并返回已发送到英国的订单：
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE shipcountry = N'UK';
You can also use the 您还可以使用INSERT SELECT statement to specify the target column names, and the recommendation I gave earlier regarding specifying those names remains the same. INSERT SELECT语句指定目标列名，我之前给出的关于指定这些名称的建议保持不变。The behavior in terms of relying on a default constraint or column nullability is also the same as with the 依赖默认约束或列可空性的行为也与INSERT VALUES statement. INSERT VALUES语句相同。The INSERT SELECT statement is performed as a transaction, so if any row fails to enter the target table, none of the rows enters the table.INSERT SELECT插入选择语句是作为事务执行的，因此，如果任何行未能进入目标表，则所有行都不会进入该表。
Note
If you include a system function such as 如果在插入的查询中包含系统函数，如SYSDATETIME in the inserted query, the function gets invoked only once for the entire query and not once per row. SYSDATETIME，则该函数在整个查询中仅被调用一次，而不是每行调用一次。The exception to this rule is if you generate globally unique identifiers (GUIDs) using the 此规则的例外情况是，如果使用每行调用的NEWID function, which gets invoked per row.NEWID函数生成全局唯一标识（GUID）。
INSERT EXEC statementINSERT EXEC语句You use the 可以使用INSERT EXEC statement to insert a result set returned from a stored procedure or a dynamic SQL batch into a target table. INSERT EXEC语句将存储过程或动态SQL批处理返回的结果集插入到目标表中。You'll find information about stored procedures, batches, and dynamic SQL in Chapter 11, “Programmable objects.” 您将在第11章“可编程对象”中找到有关存储过程、批处理和动态SQL的信息The INSERT EXEC statement is similar in syntax and concept to the INSERT SELECT statement, but instead of using a SELECT statement, you specify an EXEC statement.INSERT EXEC语句在语法和概念上与INSERT SELECT语句类似，但您可以指定EXEC语句，而不是使用SELECT语句。
For example, the following code creates a stored procedure called 例如，以下代码创建了一个名为Sales.GetOrders, and it returns orders that were shipped to a specified input country (with the @country parameter):Sales.GetOrders的存储过程，并返回发送到指定输入国家的订单（带有@country参数）：
DROP PROC IF EXISTS Sales.GetOrders;
GO
CREATE PROC Sales.GetOrders
@country AS NVARCHAR(40)
AS
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE shipcountry = @country;
GO
To test the stored procedure, execute it with the input country 要测试存储过程，请使用输入国家France:France执行它：
EXEC Sales.GetOrders @country = N'France';
You get the following output:您将获得以下输出：
orderid orderdate empid custid
----------- ----------- ----------- -----------
10248 2014-07-04 5 85
10251 2014-07-08 3 84
10265 2014-07-25 2 7
10274 2014-08-06 6 85
10295 2014-09-02 2 85
10297 2014-09-04 5 7
10311 2014-09-20 1 18
10331 2014-10-16 9 9
10334 2014-10-21 8 84
10340 2014-10-29 1 9
...
(77 row(s) affected)
By using an 通过使用INSERT EXEC statement, you can insert the result set returned from the procedure into the dbo.Orders table:INSERT EXEC语句，可以将过程返回的结果集插入dbo.Orders表：
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
EXEC Sales.GetOrders @country = N'France';
SELECT INTO statementSELECT INTO语句The SELECT INTO statement is a nonstandard T-SQL statement that creates a target table and populates it with the result set of a query. SELECT INTO语句是一个非标准的T-SQL语句，它创建一个目标表，并用查询的结果集填充它。By “nonstandard,” I mean that it's not part of the ISO and ANSI SQL standards. 所谓“非标准”，我的意思是它不是ISO和ANSI SQL标准的一部分。You cannot use this statement to insert data into an existing table. 不能使用此语句将数据插入现有表中。In terms of syntax, simply add 在语法方面，只需在要用于生成结果集的INTO <target_table_name> right before the FROM clause of the SELECT query you want to use to produce the result set. SELECT查询的FROM子句前面添加INTO <。target_table_name>For example, the following code creates a table called 例如，以下代码创建了一个名为dbo.Orders and populates it with all rows from the Sales.Orders table:dbo.Orders的表，并用Sales.Orders表中的所有行填充它：
DROP TABLE IF EXISTS dbo.Orders;
SELECT orderid, orderdate, empid, custid
INTO dbo.Orders
FROM Sales.Orders;
The target table's structure and data are based on the source table. 目标表的结构和数据基于源表。The SELECT INTO语句从源复制基本结构（例如列名、类型、可空性和标识属性）和数据。SELECT INTO statement copies from the source the base structure (such as column names, types, nullability, and identity property) and the data. It does not copy from the source constraints, indexes, triggers, column properties such as SPARSE and FILESTREAM, and permissions. 它不会复制源约束、索引、触发器、列属性（如稀疏和文件流）以及权限。If you need those in the target, you'll need to create them yourself.如果你在目标中需要这些，你需要自己创建它们。
One of the benefits of the SELECT INTO statement is its efficiency. SELECT INTO语句的好处之一是它的效率。As long as a database property called 只要名为Recovery Model is not set to FULL, this statement uses an optimized mode that applies minimal logging. Recovery Model的数据库属性未设置为FULL，该语句就会使用一种优化模式，应用最小的日志记录。This translates to a very fast operation compared to a fully logged one. 与完全记录的操作相比，这意味着操作速度非常快。Note that the 请注意，INSERT SELECT statement also can benefit from minimal logging, but the list of requirements it needs to meet is longer. INSERT SELECT语句也可以从最少的日志记录中获益，但它需要满足的需求列表更长。For details, see “Prerequisites for Minimal Logging in Bulk Import” in SQL Server Books Online at the following URL: 有关详细信息，请参阅SQL Server联机丛书中的“批量导入中最小日志记录的先决条件”，网址如下：http://msdn.microsoft.com/en-us/library/ms190422.aspx.。
If you need to use a 如果需要在集操作中使用SELECT INTO statement with set operations, you specify the INTO clause right in front of the FROM clause of the first query. SELECT INTO语句，可以在第一个查询的FROM子句前面指定INTO子句。For example, the following 例如，下面的SELECT INTO statement creates a table called Locations and populates it with the result of an EXCEPT set operation, returning locations that are customer locations but not employee locations:SELECT INTO语句创建了一个名为Locations的表，并用EXCEPT集操作的结果填充该表，返回的位置是客户位置，但不是员工位置：
DROP TABLE IF EXISTS dbo.Locations;
SELECT country, region, city
INTO dbo.Locations
FROM Sales.Customers
EXCEPT
SELECT country, region, city
FROM HR.Employees;
BULK INSERT statementBULK INSERT语句You use the 可以使用BULK INSERT statement to insert into an existing table data originating from a file. BULK INSERT语句将源于文件的数据插入到现有表中。In the statement, you specify the target table, the source file, and options. 在语句中，指定目标表、源文件和选项。You can specify many options, including the data file type (for example, 您可以指定许多选项，包括数据文件类型（例如，char or native), the field terminator, the row terminator, and others—all of which are fully documented.char或native）、字段终止符、行终止符，以及其他所有这些选项都有完整的文档记录。
For example, the following code bulk inserts the contents of the file 例如，以下代码批量将文件c:\temp\orders.txt into the table dbo.Orders, specifying that the data file type is char, the field terminator is a comma, and the row terminator is the newline character:c:\temp\orders.txt的内容插入表dbo.Orders，指定数据文件类型为char，字段终止符为逗号，行终止符为换行符：
BULK INSERT dbo.Orders FROM 'c:\temp\orders.txt'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
);
Note that if you want to actually run this statement, you need to place the 请注意，如果要实际运行此语句，需要将随本书源代码一起提供的orders.txt file provided along with the source code for this book into the c:\temp folder.orders.txt文件放入c:\temp文件夹。
You can run the 在某些情况下，只要满足某些要求，就可以以快速、最少记录的模式运行BULK INSERT statement in a fast, minimally logged mode in certain scenarios as long as certain requirements are met. BULK INSERT语句。For details, see “Prerequisites for Minimal Logging in Bulk Import” in SQL Server Books Online.有关详细信息，请参阅SQL Server联机丛书中的“批量导入中最小日志记录的先决条件”。
SQL Server supports two built-in solutions to automatically generate numeric keys: the identity column property and the sequence object. SQL Server支持两种内置解决方案来自动生成数字键：标识列属性和序列对象。The identity property works well for some scenarios, but it also has many limitations. 标识属性适用于某些场景，但也有许多局限性。The sequence object resolves many of the identity property's limitations. 序列对象解决了标识属性的许多限制。I'll start with identity.我从身份开始。
Identity is a standard column property. 标识是标准的列属性。You can define this property for a column with any numeric type with a scale of zero (no fraction). 可以为具有任何数字类型且刻度为零（无分数）的列定义此属性。When defining the property, you can optionally specify a seed (the first value) and an increment (a step value). 定义特性时，可以选择指定种子（第一个值）和增量（步长值）。If you don't provide those, the default is 如果不提供这些，则默认值为1 for both. 1。You typically use this property to generate通常使用此属性生成“代理密钥”，这些密钥由系统生成，并且不是从应用程序数据派生的。 surrogate keys, which are keys that are produced by the system and are not derived from the application data.
For example, the following code creates a table called 例如，以下代码创建了一个名为dbo.T1:dbo.T1的表：
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_T1 PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
CONSTRAINT CHK_T1_datacol CHECK(datacol LIKE '[ABCDEFGHIJKLMNOPQRSTUVWXYZ]%')
);
The table contains a column called 该表包含一个名为keycol that is defined with an identity property using 1 as the seed and 1 as the increment. keycol的列，该列使用标识属性定义，使用1作为种子，1作为增量。The table also contains a character string column called 该表还包含一个名为datacol, whose data is restricted with a CHECK constraint to strings starting with an alphabetical character.datacol的字符串列，该列的数据通过CHECK约束限制为以字母字符开头的字符串。
In your 在INSERT statements, you must completely ignore the identity column. INSERT语句中，必须完全忽略标识列。For example, the following code inserts three rows into the table, specifying values only for the column 例如，以下代码在表中插入三行，仅为列datacol:datacol指定值：
INSERT INTO dbo.T1(datacol) VALUES('AAAAA'),('CCCCC'),('BBBBB');
SQL Server produced the values for SQL Server自动生成keycol automatically. keycol的值。Query the table to see the values that were generated:查询该表以查看生成的值：
SELECT * FROM dbo.T1;
You get the following output:
keycol datacol
----------- ----------
1 AAAAA
2 CCCCC
3 BBBBB
When you query the table, naturally you can refer to the identity column by its name (查询表时，自然可以通过名称（本例中为keycol in this case). keycol）引用标识列。SQL Server also provides a way to refer to the identity column by using the more generic form SQL Server还提供了一种通过使用更通用的形式$identity.$identity来引用标识列的方法。
For example, the following query selects the identity column from 例如，以下查询使用通用形式从T1 by using the generic form:T1中选择标识列：
SELECT $identity FROM dbo.T1;
This query returns the following output:此查询返回以下输出：
keycol
-----------
1
2
3
When you insert a new row into the table, SQL Server generates a new identity value based on the current identity value in the table and the increment. 在表中插入新行时，SQL Server会根据表中的当前标识值和增量生成一个新标识值。If you need to obtain the newly generated identity value—for example, to insert child rows into a referencing table—you query one of two functions, called 例如，如果需要获取新生成的标识值，要在引用表中插入子行，可以查询两个函数之一，即@@identity and SCOPE_IDENTITY.@@identity和SCOPE_IDENTITY。
The @@identity function returns the last identity value generated by the session, regardless of scope (for example, a procedure issuing an INSERT statement, and a trigger fired by that statement are in different scopes). @@identity函数返回会话生成的最后一个标识值，而不考虑作用域（例如，发出INSERT语句的过程和该语句触发的触发器位于不同的作用域）。SCOPE_IDENTITY returns the last identity value generated by the current scope (for example, the same procedure). SCOPE_IDENTITY返回当前作用域生成的最后一个标识值（例如，相同的过程）。Except in the rare cases when you don't really care about scope, you should use the 除了在少数情况下，当你并不真正关心范围时，你应该使用SCOPE_IDENTITY function.SCOPE_IDENTITY函数。
For example, the following code inserts a new row into the table 例如，以下代码在表T1, obtains the newly generated identity value and places it into a variable by querying the SCOPE_IDENTITY function, and queries the variable:T1中插入新行，通过查询SCOPE_IDENTITY函数获得新生成的标识值并将其放入变量中，然后查询变量：
DECLARE @new_key AS INT;
INSERT INTO dbo.T1(datacol) VALUES('AAAAA');
SET @new_key = SCOPE_IDENTITY();
SELECT @new_key AS new_key
If you ran all previous code samples provided in this section, this code returns the following output:如果运行本节中提供的所有之前的代码示例，则此代码将返回以下输出：
new_key
-----------
4
Remember that both 记住@@identity and SCOPE_IDENTITY return the last identity value produced by the current session. Neither is affected by inserts issued by other sessions. @@identity和SCOPE_IDENTITY都返回当前会话生成的最后一个标识值。两者都不受其他会议发布的插页的影响。However, if you want to know the current identity value in a table (the last value produced) regardless of session, you should use the 但是，如果您想知道表中的当前标识值（生成的最后一个值），而不考虑会话，则应使用IDENT_CURRENT function and provide the table name as input. IDENT_CURRENT函数并提供表名作为输入。For example, run the following code from a new session (not the one from which you ran the previous 例如，从一个新会话（而不是运行前面INSERT statements):INSERT语句的会话）运行以下代码：
SELECT
SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
@@identity AS [@@identity],
IDENT_CURRENT(N'dbo.T1') AS [IDENT_CURRENT];
You get the following output:您将获得以下输出：
SCOPE_IDENTITY @@identity IDENT_CURRENT
---------------- ------------ -------------
NULL NULL 4
Both @@identity and SCOPE_IDENTITY returned NULLs because no identity values were created in the session in which this query ran. @@identity和SCOPE_IDENTITY都返回null，因为在运行此查询的会话中没有创建标识值。IDENT_CURRENT returned the value 4 because it returns the current identity value in the table, regardless of the session in which it was produced.IDENT_CURRENT返回值4，因为它返回表中的当前标识值，而不管它是在哪个会话中生成的。
There's a certain part of the design of the identity property that comes as a surprise to some. 标识属性的设计中有一部分让一些人感到惊讶。The change to the current identity value in a table is not undone if the 如果生成更改的INSERT that generated the change fails or the transaction in which the statement runs is rolled back. INSERT失败，或者回滚了运行该语句的事务，则不会撤消对表中当前标识值的更改。For example, run the following 例如，运行以下INSERT statement, which conflicts with the CHECK constraint defined in the table:INSERT语句，该语句与表中定义的CHECK约束冲突：
INSERT INTO dbo.T1(datacol) VALUES('12345');
The insert fails, and you get the following error:插入失败，出现以下错误：
Msg 547, Level 16, State 0, Line 159
The INSERT statement conflicted with the CHECK constraint "CHK_T1_datacol". The conflict
occurred in database "TSQLV4", table "dbo.T1", column 'datacol'.
The statement has been terminated.
Even though the insert failed, the current identity value in the table changed from 即使插入失败，表中的当前标识值也从4 to 5, and this change was not undone because of the failure. 4更改为5，并且由于失败，此更改未撤消。This means that the next insert will produce the value 这意味着下一次插入将产生值6:6：
INSERT INTO dbo.T1(datacol) VALUES('EEEEE');
Query the table:
SELECT * FROM dbo.T1;
Notice a gap between the values 请注意输出中的值4 and 6 in the output:4和6之间的间隙：
keycol datacol
----------- ----------
1 AAAAA
2 CCCCC
3 BBBBB
4 AAAAA
6 EEEEE
Also, SQL Server uses a performance cache feature for the identity property, which can result in gaps between the keys when there's an unclean termination of the SQL Server process—for example, because of a power failure. 此外，SQL Server对identity属性使用性能缓存功能，这可能会在SQL Server进程不干净地终止时（例如，由于电源故障）导致密钥之间出现间隙。As you might realize, you should use the identity property only if you can allow gaps between the keys. 正如您可能意识到的，只有在允许键之间存在间隙的情况下，才应该使用identity属性。Otherwise, you should implement your own mechanism to generate keys.否则，您应该实现自己的机制来生成密钥。
One of the shortcomings of the identity property is that you cannot add it to an existing column or remove it from an existing column. 标识属性的缺点之一是无法将其添加到现有列或从现有列中删除。If you need to make such a change, it's an expensive and cumbersome offline operation.如果你需要做这样的改变，这是一个昂贵而繁琐的离线操作。
With SQL Server, you can specify your own explicit values for the identity column when you insert rows, as long as you enable a session option called 使用SQL Server，您可以在插入行时为标识列指定自己的显式值，只要对所涉及的表启用名为IDENTITY_INSERT against the table involved. IDENTITY_INSERT的会话选项。There's no option you can use to update an identity column, though.不过，您无法使用任何选项来更新标识列。
For example, the following code demonstrates how to insert a row into 例如，下面的代码演示了如何在T1 with the explicit value 5 in keycol:T1中插入一行，其中keycol中的显式值为5：
SET IDENTITY_INSERT dbo.T1 ON;
INSERT INTO dbo.T1(keycol, datacol) VALUES(5, 'FFFFF');
SET IDENTITY_INSERT dbo.T1 OFF;
Interestingly, when you turn off the 有趣的是，当您关闭IDENTITY_INSERT option, SQL Server changes the current identity value in the table only if the explicit value you provided is greater than the current identity value. IDENTITY_INSERT选项时，只有当您提供的显式值大于当前标识值时，SQL Server才会更改表中的当前标识值。Because the current identity value in the table prior to running the preceding code was 因为在运行前面的代码之前，表中的当前标识值是6, and the INSERT statement in this code used the lower explicit value 5, the current identity value in the table did not change. 6，并且该代码中的INSERT语句使用了较低的显式值5，所以表中的当前标识值没有更改。So if at this point you query the 因此，如果此时查询该表的IDENT_CURRENT function for this table, you will get 6 and not 5. IDENT_CURRENT函数，将得到6，而不是5。This way, the next 这样，针对该表的下一条INSERT statement against the table will produce the value 7:INSERT语句将生成值7：
INSERT INTO dbo.T1(datacol) VALUES('GGGGG');
Query the current contents of the table 查询表T1:T1的当前内容：
SELECT * FROM dbo.T1;
You get the following output:您将获得以下输出：
keycol datacol
----------- ----------
1 AAAAA
2 CCCCC
3 BBBBB
4 AAAAA
5 FFFFF
6 EEEEE
7 GGGGG
You need to understand that the identity property itself does not enforce uniqueness in the column. 您需要了解，标识属性本身并不强制列中的唯一性。I already explained that you can provide your own explicit values after setting the 我已经解释过，在将IDENTITY_INSERT option to ON, and those values can be ones that already exist in rows in the table. IDENTITY_INSERT选项设置为ON之后，可以提供自己的显式值，这些值可以是表中已经存在的行。Also, you can reseed the current identity value in the table by using the 此外，可以使用DBCC CHECKIDENT command—for syntax, see “DBCC CHECKIDENT (Transact-SQL)” in SQL Server Books Online at the following URL: DBCC CHECKIDENT命令在表中重新设定当前标识值的种子。有关语法，请参阅SQL Server联机丛书中的“DBCC CHECKIDENT（Transact-SQL）”，网址如下：https://msdn.microsoft.com/en-us/library/ms176057.aspx. 。If you need to guarantee uniqueness in an identity column, make sure you also define a primary key or a unique constraint on that column.如果需要保证标识列中的唯一性，请确保还定义了该列上的主键或唯一约束。
T-SQL supports the standard sequence object as an alternative key-generating mechanism for identity. T-SQL支持标准序列对象作为身份的替代密钥生成机制。The sequence object is more flexible than identity in many ways, making it the preferred choice in many cases.序列对象在许多方面比身份更灵活，因此在许多情况下它是首选。
One of the advantages of the sequence object is that, unlike identity, it's not tied to a particular column in a particular table; rather, it's an independent object in the database. 序列对象的优点之一是，与标识不同，它不绑定到特定表中的特定列；相反，它是数据库中的一个独立对象。Whenever you need to generate a new value, you invoke a function against the object and use the returned value wherever you like. 无论何时需要生成新值，都可以对对象调用函数，并在任何地方使用返回的值。For example, if you have such a use case, you can use one sequence object that will help you maintain keys that will not conflict across multiple tables.例如，如果您有这样一个用例，您可以使用一个序列对象来帮助您维护不会在多个表之间冲突的键。
To create a sequence object, use the 要创建序列对象，请使用CREATE SEQUENCE command. CREATE SEQUENCE命令。The minimum required information is just the sequence name, but note that the defaults for the various properties in such a case might not be what you want. 所需的最少信息只是序列名，但请注意，在这种情况下，各种属性的默认值可能不是您想要的。If you don't indicate the data type, SQL Server will use 如果不指明数据类型，SQL Server将默认使用BIGINT by default. BIGINT。If you want a different type, indicate 如果需要其他类型，请指示为AS <type>. <type>。The type can be any numeric type with a scale of zero. 该类型可以是刻度为零的任何数字类型。For example, if you need your sequence to be of an 例如，如果您需要序列为INT type, indicate AS INT.INT类型，请指示为AS INT。
Unlike the identity property, the sequence object supports the specification of a minimum value (与标识属性不同，序列对象支持在类型中指定最小值（MINVALUE <val>) and a maximum value (MAXVALUE <val>) within the type. MINVALUE <val>）和最大值（MAXVALUE <val>）。If you don't indicate what the minimum and maximum values are, the sequence object will assume the minimum and maximum values supported by the type. 如果不指明最小值和最大值是什么，序列对象将采用该类型支持的最小值和最大值。For example, for an 例如，对于INT type, those would be –2,147,483,648 and 2,147,483,647, respectively.INT类型，它们分别是-2147483648和2147483647。
Also, unlike identity, the sequence object supports cycling. 此外，与标识不同，序列对象支持循环。Note, though, that the default is 不过，请注意，默认设置为NO CYCLE. NO CYCLE（非循环）。If you want the sequence object to cycle, you need to be explicit about it by using the 如果希望序列对象循环，则需要使用CYCLE option.CYCLE选项对其进行明确说明。
Like identity, the sequence object allows you to specify the starting value (与标识类似，序列对象允许您指定起始值（START WITH <val>) and the increment (INCREMENT BY <val>). START WITH <val>）和增量（INCREMENT BY <val>）。If you don't indicate the starting value, the default will be the same as the minimum value (如果不指明起始值，默认值将与最小值（MINVALUE). MINVALUE）相同。If you don't indicate the increment value, it will be 如果不指示增量值，则默认值为1 by default.1。
For example, suppose you want to create a sequence that will help you generate order IDs. 例如，假设您想要创建一个序列来帮助您生成订单ID。You want it to be of an 您希望它是INT type, have a minimum value of 1 and a maximum value that is the maximum supported by the type, start with 1, increment by 1, and allow cycling. INT类型，最小值为1，最大值为该类型支持的最大值，从1开始，递增1，并允许循环。Here's the 下面是创建序列的CREATE SEQUENCE command you would use to create such a sequence:CREATE SEQUENCE命令：
CREATE SEQUENCE dbo.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;
You had to be explicit about the type, minimum value, and cycling option because they are different than the defaults. 必须明确说明类型、最小值和循环选项，因为它们与默认值不同。You didn't need to indicate the maximum, start with, and increment values because you wanted the defaults.您不需要指出最大值、起始值和增量值，因为您需要默认值。
The sequence object also supports a caching option (sequence对象还支持一个缓存选项（CACHE <val> | NO CACHE) that tells SQL Server how often to write the recoverable value to disk. CACHE <val> | NO CACHE），该选项告诉SQL Server将可恢复值写入磁盘的频率。For example, if you specify a cache value of 10,000, SQL Server will write to disk every 10,000 requests, and in between disk writes, it will maintain the current value and how many values are left in memory. 例如，如果指定缓存值为10000，SQL Server将每10000个请求向磁盘写入一次，在磁盘写入之间，它将保持当前值以及内存中剩余的值。If you write less frequently to disk, you'll get better performance when generating a value (on average), but you'll risk losing more values in case of an unexpected termination of the SQL Server process, such as in a power failure. 如果您对磁盘的写入频率较低，那么在生成值时（平均而言），您将获得更好的性能，但如果SQL Server进程意外终止，例如发生电源故障，您将面临丢失更多值的风险。SQL Server has a default cache value of 50, although this number is not officially documented because Microsoft wants to be able to change it.SQL Server的默认缓存值为50，但由于Microsoft希望能够更改该值，因此没有正式记录该值。
You can change any of the sequence properties except the data type with the 可以使用ALTER SEQUENCE command (MINVAL <val>, MAXVAL <val>, RESTART WITH <val>, INCREMENT BY <val>, CYCLE | NO CYCLE, or CACHE <val> | NO CACHE). ALTER SEQUENCE命令（MINVAL <val>、MAXVAL <val>、RESTART WITH <val>、INCREMENT BY <val>、CYCLE | NO CYCLE或CACHE <val> | NO CACHE）更改除数据类型之外的任何序列属性。For example, suppose you want to prevent the sequence 例如，假设您想要阻止序列dbo.SeqOrderIDs from cycling. dbo.SeqOrderIDs循环。You can change the current sequence definition with the following 可以使用以下ALTER SEQUENCE command:ALTER SEQUENCE命令更改当前序列定义：
ALTER SEQUENCE dbo.SeqOrderIDs
NO CYCLE;
To generate a new sequence value, you need to invoke the standard function 要生成新的序列值，需要调用标准函数NEXT VALUE FOR <sequence name>. NEXT VALUE FOR <sequence name>。Here's an example of invoking the function:下面是调用函数的示例：
SELECT NEXT VALUE FOR dbo.SeqOrderIDs;
This code generates the following output:此代码生成以下输出：
-----------
1
Notice that, unlike with identity, you didn't need to insert a row into a table in order to generate a new value. 请注意，与identity不同，生成新值不需要在表中插入行。Some applications need to generate the new value before using it. 有些应用程序在使用之前需要生成新的值。With sequences, you can store the result of the function in a variable and use it later in the code. 使用序列，可以将函数的结果存储在变量中，并在以后的代码中使用。To demonstrate this, first create a table called 为了演示这一点，首先使用以下代码创建一个名为T1 with the following code:T1的表：
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
The following code generates a new sequence value, stores it in a variable, and then uses the variable in an 以下代码生成一个新的序列值，将其存储在变量中，然后在INSERT statement to insert a row into the table:INSERT语句中使用该变量将一行插入表中：
DECLARE @neworderid AS INT = NEXT VALUE FOR dbo.SeqOrderIDs;
INSERT INTO dbo.T1(keycol, datacol) VALUES(@neworderid, 'a');
SELECT * FROM dbo.T1;
This code returns the following output:此代码返回以下输出：
keycol datacol
----------- ----------
2 a
If you need to use the new key in related rows that you add to another table, you can use the variable when you insert those rows.如果需要在添加到另一个表的相关行中使用新键，可以在插入这些行时使用变量。
If you don't need to generate the new sequence value before using it, you can specify the 如果在使用前不需要生成新的序列值，可以直接在NEXT VALUE FOR function directly as part of your INSERT statement, like this:INSERT语句中指定NEXT VALUE FOR函数，如下所示：
INSERT INTO dbo.T1(keycol, datacol)
VALUES(NEXT VALUE FOR dbo.SeqOrderIDs, 'b');
SELECT * FROM dbo.T1;
This code returns the following output:此代码返回以下输出：
keycol datacol
----------- ----------
2 a
3 b
Unlike with identity, you can generate new sequence values in an 与标识不同，您可以在UPDATE statement, like this:UPDATE语句中生成新的序列值，如下所示：
UPDATE dbo.T1
SET keycol = NEXT VALUE FOR dbo.SeqOrderIDs;
SELECT * FROM dbo.T1;
This code returns the following output:此代码返回以下输出：
keycol datacol
----------- ----------
4 a
5 b
To get information about your sequences, query a view called 要获取有关序列的信息，请查询名为sys.sequences. sys.sequences的视图。For example, to find the current sequence value in the 例如，要在SeqOrderIDs sequence, you use the following code:SeqOrderIDs序列中查找当前序列值，可以使用以下代码：
SELECT current_value
FROM sys.sequences
WHERE OBJECT_ID = OBJECT_ID(N'dbo.SeqOrderIDs');
This code generates the following output:此代码生成以下输出：
current_value
--------------
5
SQL Server extends its support for the sequence option with capabilities beyond what the competitors and the standard currently support. SQL Server将其对序列选项的支持扩展到了竞争对手和标准当前支持的功能之外。One of the extensions enables you to control the order of the assigned sequence values in a multirow insert by using an 其中一个扩展允许您使用OVER clause. OVER子句控制多行插入中指定序列值的顺序。Here's an example:下面是一个例子：
INSERT INTO dbo.T1(keycol, datacol)
SELECT
NEXT VALUE FOR dbo.SeqOrderIDs OVER(ORDER BY hiredate),
LEFT(firstname, 1) + LEFT(lastname, 1)
FROM HR.Employees;
SELECT * FROM dbo.T1;
This code returns the following output:此代码返回以下输出：
keycol datacol
----------- ----------
4 a
5 b
6 JL
7 SD
8 DF
9 YP
10 SM
11 PS
12 RK
13 MC
14 PD
Another extension to the standard allows the use of the 该标准的另一个扩展允许在默认约束中使用NEXT VALUE FOR function in a default constraint. NEXT VALUE FOR函数。Here's an example:下面是一个例子：
ALTER TABLE dbo.T1
ADD CONSTRAINT DFT_T1_keycol
DEFAULT (NEXT VALUE FOR dbo.SeqOrderIDs)
FOR keycol;
Now when you insert rows into the table, you don't have to indicate a value for 现在，在表中插入行时，不必为keycol:keycol指定值：
INSERT INTO dbo.T1(datacol) VALUES('c');
SELECT * FROM dbo.T1;
This code returns the following output:此代码返回以下输出：
keycol datacol
----------- ----------
4 a
5 b
6 JL
7 SD
8 DF
9 YP
10 SM
11 PS
12 RK
13 MC
14 PD
15 c
Unlike with identity, which you cannot add to or remove from an existing column, you can add or remove a default constraint. 与不能在现有列中添加或删除标识不同，可以添加或删除默认约束。The preceding example showed how to add a default constraint to a table and associate it with a column. 前面的示例演示了如何向表中添加默认约束并将其与列关联。To remove a constraint, use the syntax: 要删除约束，请使用以下语法：ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>.ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>。
There's another extension to the standard you can use to allocate a whole range of sequence values at once by using a stored procedure called 该标准还有另一个扩展，您可以使用名为sp_sequence_get_range. sp_sequence_get_range的存储过程一次分配整个序列值范围。The idea is that if the application needs to assign a range of sequence values, it's efficient to update the sequence only once, incrementing it by the size of the range. 其思想是，如果应用程序需要分配一个序列值范围，只需更新一次序列，将其按范围大小递增即可。You call the procedure, indicate the size of the range you want, and collect the first value in the range, as well as other information, by using output parameters. 调用该过程，指示所需范围的大小，并使用输出参数收集范围中的第一个值以及其他信息。Here's an example of calling the procedure and asking for a range of 1,000,000 sequence values:下面是调用该过程并请求1000000个序列值范围的示例：
DECLARE @first AS SQL_VARIANT;
EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.SeqOrderIDs',
@range_size = 1000000,
@range_first_value = @first OUTPUT ;
SELECT @first;
Note
SQL_VARIANT is a generic data type that can hold within it various base data types. SQL_VARIANT是一种通用数据类型，可以容纳各种基本数据类型。The sp_sequence_get_range procedure uses this type for several of its parameters, including the output parameter @range_first_value. sp_sequence_get_range过程对其几个参数使用这种类型，包括输出参数@range_first_value。For details about this data type, see SQL Server Books Online at the following URL: 有关此数据类型的详细信息，请参阅SQL Server联机丛书，网址如下：https://msdn.microsoft.com/en-us/library/ms173829.aspx.。
If you run the code twice, you will find that the returned first value in the second call is greater than the first by 1,000,000.如果您运行代码两次，您会发现第二次调用中返回的第一个值比第一个值大1000000。
Note that like with identity, the sequence object does not guarantee you will have no gaps. 请注意，与标识一样，序列对象不能保证没有间隙。If a new sequence value was generated by a transaction that failed or intentionally rolled back, the sequence change is not undone. 如果新的序列值是由失败或有意回滚的事务生成的，则序列更改不会撤消。Also, as mentioned earlier with identity, sequence objects support a performance cache feature, which can result in gaps when there's an unclean termination of the SQL Server process.此外，正如前面提到的标识，序列对象支持性能缓存功能，当SQL Server进程不干净地终止时，这可能会导致出现间隙。
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TABLE IF EXISTS dbo.T1;
DROP SEQUENCE IF EXISTS dbo.SeqOrderIDs;
T-SQL provides two statements for deleting rows from a table: T-SQL提供了两条用于从表中删除行的语句：DELETE and TRUNCATE. DELETE和TRUNCATE。This section describes those statements. 本节介绍这些陈述。The examples I provide in this section are applied against copies of the 我在本节中提供的示例应用于dbo模式中创建的Customers and Orders tables from the Sales schema created in the dbo schema. Sales架构中的Customers和Orders表的副本。Run the following code to create and populate those tables:运行以下代码来创建和填充这些表：
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;
CREATE TABLE dbo.Customers
(
custid INT NOT NULL,
companyname NVARCHAR(40) NOT NULL,
contactname NVARCHAR(30) NOT NULL,
contacttitle NVARCHAR(30) NOT NULL,
address NVARCHAR(60) NOT NULL,
city NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
postalcode NVARCHAR(10) NULL,
country NVARCHAR(15) NOT NULL,
phone NVARCHAR(24) NOT NULL,
fax NVARCHAR(24) NULL,
CONSTRAINT PK_Customers PRIMARY KEY(custid)
);
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
custid INT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL,
requireddate DATE NOT NULL,
shippeddate DATE NULL,
shipperid INT NOT NULL,
freight MONEY NOT NULL
CONSTRAINT DFT_Orders_freight DEFAULT(0),
shipname NVARCHAR(40) NOT NULL,
shipaddress NVARCHAR(60) NOT NULL,
shipcity NVARCHAR(15) NOT NULL,
shipregion NVARCHAR(15) NULL,
shippostalcode NVARCHAR(10) NULL,
shipcountry NVARCHAR(15) NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid)
REFERENCES dbo.Customers(custid)
);
GO
INSERT INTO dbo.Customers SELECT * FROM Sales.Customers;
INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;
DELETE statementDELETE语句The DELETE statement is a standard statement used to delete data from a table based on an optional filter predicate. DELETE语句是一种标准语句，用于根据可选的筛选谓词从表中删除数据。The standard statement has only two clauses—the 标准语句只有两个子句：FROM clause, in which you specify the target table name, and a WHERE clause, in which you specify a predicate. FROM子句和WHERE子句，前者指定目标表名，后者指定谓词。Only the subset of rows for which the predicate evaluates to 只有谓词计算结果为TRUE will be deleted.TRUE的行子集才会被删除。
For example, the following statement deletes, from the 例如，以下语句从dbo.Orders table, all orders that were placed prior to 2015:dbo.Orders表中删除2015年之前下的所有订单：
DELETE FROM dbo.Orders
WHERE orderdate < '20150101';
Run this statement. 运行此语句。SQL Server will report that it deleted 152 rows:SQL Server将报告已删除152行：
(152 row(s) affected)
Note that you can suppress returning the message that indicates the number of rows affected by turning on the session option 请注意，您可以禁止返回指示受打开会话选项NOCOUNT. NOCOUNT影响的行数的消息。As you probably noticed, this option is off by default.正如您可能注意到的，此选项在默认情况下处于禁用状态。
The 删除大量行时，DELETE statement tends to be expensive when you delete a large number of rows, mainly because it's a fully logged operation.DELETE语句的开销往往很大，这主要是因为它是一个完全记录的操作。
TRUNCATE statementTRUNCATE语句The standard 标准TRUNCATE statement deletes all rows from a table. TRUNCATE语句删除表中的所有行。Unlike the 与DELETE statement, TRUNCATE has no filter. DELETE语句不同，TRUNCATE没有筛选器。For example, to delete all rows from a table called 例如，要从名为dbo.T1, you run the following code:dbo.T1的表中删除所有行，请运行以下代码：
TRUNCATE TABLE dbo.T1;
The advantage that 与TRUNCATE has over DELETE is that the former is minimally logged, whereas the latter is fully logged, resulting in significant performance differences. DELETE相比，TRUNCATE的优点是前者的日志记录最少，而后者的日志记录完全，这导致了显著的性能差异。For example, if you use the 例如，如果使用TRUNCATE statement to delete all rows from a table with millions of rows, the operation will finish in a matter of seconds. TRUNCATE语句从包含数百万行的表中删除所有行，操作将在几秒钟内完成。If you use the 如果使用DELETE statement, the operation can take many minutes. DELETE语句，操作可能需要很多分钟。Note that I said 请注意，我说过TRUNCATE is minimally logged, as opposed to not being logged at all. TRUNCATE的日志记录最少，而不是根本不被记录。SQL Server records which blocks of data were deallocated by the operation so that it can reclaim those in case the transaction needs to be undone. SQL Server记录操作释放的数据块，以便在需要撤消事务时可以回收这些数据块。Both DELETE and TRUNCATE are transactional.DELETE和TRUNCATE都是事务性的。
TRUNCATE and DELETE also have a functional difference when the table has an identity column. TRUNCATE和DELETE也有功能上的区别。TRUNCATE resets the identity value back to the original seed, but DELETE doesn't—even when used without a filter. TRUNCATE将标识值重置回原始种子，但DELETE不会重置，即使在没有筛选器的情况下使用。Interestingly, the standard defines an 有趣的是，该标准为identity column restart option for the TRUNCATE statement, which you use to control whether to restart or continue the identity value, but unfortunately T-SQL doesn't support this option.TRUNCATE语句定义了一个“标识列重新启动选项”，您可以使用它来控制是重新启动还是继续标识值，但不幸的是，T-SQL不支持此选项。
The 当目标表被外键约束引用时，即使引用表为空且外键已禁用，也不允许使用TRUNCATE statement is not allowed when the target table is referenced by a foreign-key constraint, even if the referencing table is empty and even if the foreign key is disabled. TRUNCATE语句。The only way to allow a 允许TRUNCATE statement is to drop all foreign keys referencing the table with the ALTER TABLE DROP CONSTRAINT command. TRUNCATE语句的唯一方法是使用ALTER TABLE DROP CONSTRAINT命令删除引用表的所有外键。You can then re-create the foreign keys after truncating the table with the 然后，可以在使用ALTER TABLE ADD CONSTRAINT command.ALTER TABLE ADD CONSTRAINT命令截断表后重新创建外键。
Accidents such as truncating or dropping the incorrect table can happen. 可能会发生截断或删除错误表等事故。For example, let's say you have connections open against both the production and the development environments, and you submit your code in the wrong connection. 例如，假设您在生产环境和开发环境中都打开了连接，并且您在错误的连接中提交了代码。Both the TRUNCATE and DROP statements are so fast that the transaction is committed before you realize your mistake. TRUNCATE和DROP语句的速度都非常快，以至于在您意识到错误之前就提交了事务。To prevent such accidents, you can protect a production table by simply creating a dummy table with a foreign key pointing to that table. 为了防止此类事故，只需创建一个外键指向生产表的伪表，就可以保护生产表。You can even disable the foreign key so that it won't have any impact on performance. 您甚至可以禁用外键，这样就不会对性能产生任何影响。As I mentioned earlier, even when disabled, this foreign key prevents you from truncating or dropping the referenced table.正如我前面提到的，即使禁用了，这个外键也会阻止您截断或删除引用的表。
In case you have partitioned tables in your database, SQL Server 2016 enhances the 如果数据库中有分区表，SQL Server 2016将通过支持单个分区的截断来增强TRUNCATE statement by supporting the truncation of individual partitions. TRUNCATE语句。You can specify a list of partitions and partition ranges (with the keyword 可以指定分区和分区范围的列表（在范围分隔符之间使用关键字TO between the range delimiters). TO）。As an example, suppose you had a partitioned table called 例如，假设您有一个名为T1 and you wanted to truncate partitions 1, 3, 5, and 7 through 10. T1的分区表，并且希望截断分区1、3、5和7到10。You would use the following code to achieve this:您可以使用以下代码来实现这一点：
TRUNCATE TABLE dbo.T1 WITH ( PARTITIONS(1, 3, 5, 7 TO 10) );
Note
Table partitioning is about dividing your table into multiple units called 表分区是将表划分为多个称为“分区”的单元，主要用于可管理性目的。partitions, mainly for manageability purposes. This allows handling processes like importing data into the table and purging historic data to be handled more efficiently. 这样可以更有效地处理将数据导入表和清除历史数据等过程。You can find details on the topic in SQL Server Books Online at the following URL: 您可以在以下URL的SQL Server联机丛书中找到有关该主题的详细信息：https://msdn.microsoft.com/en-us/library/ms190787.aspx.。
DELETE based on a joinDELETET-SQL supports a nonstandard T-SQL支持基于连接的非标准DELETE syntax based on joins. DELETE语法。The join serves a filtering purpose and also gives you access to the attributes of the related rows from the joined tables. 联接用于筛选目的，还允许您访问联接表中相关行的属性。This means you can delete rows from one table based on a filter against attributes in related rows from another table.这意味着您可以根据针对另一个表中相关行的属性的筛选器从一个表中删除行。
For example, the following statement deletes orders placed by customers from the United States:例如，以下语句删除来自美国的客户下的订单：
DELETE FROM O
FROM dbo.Orders AS O
INNER JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE C.country = N'USA';
Much like in a 与SELECT statement, the first clause that is logically processed in a DELETE statement is the FROM clause (the second one that appears in this statement). SELECT语句类似，DELETE语句中逻辑处理的第一个子句是FROM子句（该语句中出现的第二个子句）。Then the 然后处理WHERE clause is processed, and finally the DELETE clause. WHERE子句，最后是DELETE子句。In our case, the statement first joins the 在例子中，该语句首先根据订单的客户ID和客户的客户ID之间的匹配，将Orders table (aliased as O) with the Customers table (aliased as C) based on a match between the order's customer ID and the customer's customer ID. Orders表（别名为O）与Customers表（别名为C）连接起来。It then filters only orders placed by customers from the United States. 然后，它只筛选来自美国的客户下的订单。Finally, the statement deletes all qualifying rows from 最后，该语句从O (the alias representing the Orders table).O（代表Orders表的别名）中删除所有符合条件的行。
The two 基于连接的FROM clauses in a DELETE statement based on a join might be confusing. DELETE语句中的两个FROM子句可能会令人困惑。But when you develop the code, develop it as if it were a 但在开发代码时，要像开发带有连接的SELECT statement with a join. SELECT语句一样进行开发。That is, start with the 也就是说，从带有连接的FROM clause with the joins, move on to the WHERE clause, and finally—instead of specifying a SELECT clause—specify a DELETE clause with the alias of the side of the join that is supposed to be the target for the deletion. FROM子句开始，转到WHERE子句，最后，不要指定SELECT子句，而是指定一个DELETE子句，该子句的别名为连接的一方，该方应该是删除的目标。Note that the first 请注意，第一个FROM clause is optional. FROM子句是可选的。In our example, you can specify 在示例中，可以指定DELETE O instead of DELETE FROM O.DELETE O而不是DELETE FROM O。
As I mentioned earlier, a 正如我前面提到的，基于连接的DELETE statement based on a join is nonstandard. DELETE语句是不标准的。If you want to stick to standard code, you can use subqueries instead of joins. 如果您想坚持使用标准代码，可以使用子查询而不是联接。For example, the following 例如，以下DELETE statement uses a subquery to achieve the same task:DELETE语句使用子查询来实现相同的任务：
DELETE FROM dbo.Orders
WHERE EXISTS
(SELECT *
FROM dbo.Customers AS C
WHERE Orders.custid = C.custid
AND C.country = N'USA');
This code deletes all rows from the 此代码删除Orders table for which a related customer from the United States exists in the Customers table.Orders表中Customers表中存在美国相关客户的所有行。
SQL Server processes the two queries the same way (using the same query execution plan); therefore, you shouldn't expect any performance difference between them. SQL Server以相同的方式处理这两个查询（使用相同的查询执行计划）；因此，您不应该期望它们之间有任何性能差异。I usually recommend sticking to the standard as much as possible unless you have a compelling reason to do otherwise—for example, in the case of a performance difference.我通常建议尽可能地坚持标准，除非你有令人信服的理由去做，例如，在性能差异的情况下。
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;
T-SQL supports a standard T-SQL支持一个标准的UPDATE statement you can use to update rows in a table. UPDATE语句，可以用来更新表中的行。T-SQL also supports nonstandard forms of the T-SQL还支持带有连接和变量的非标准形式的UPDATE statement with joins and with variables. UPDATE语句。This section describes the different forms of the statement.本节描述了语句的不同形式。
The examples I provide in this section are against copies of the 我在本节中提供的示例是针对在Orders and OrderDetails tables from the Sales schema created in the dbo schema. dbo架构中创建的Sales模式中的Orders和OrderDetails表的副本。Run the following code to create and populate those tables:运行以下代码来创建和填充这些表：
DROP TABLE IF EXISTS dbo.OrderDetails, dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
custid INT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL,
requireddate DATE NOT NULL,
shippeddate DATE NULL,
shipperid INT NOT NULL,
freight MONEY NOT NULL
CONSTRAINT DFT_Orders_freight DEFAULT(0),
shipname NVARCHAR(40) NOT NULL,
shipaddress NVARCHAR(60) NOT NULL,
shipcity NVARCHAR(15) NOT NULL,
shipregion NVARCHAR(15) NULL,
shippostalcode NVARCHAR(10) NULL,
shipcountry NVARCHAR(15) NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
CREATE TABLE dbo.OrderDetails
(
orderid INT NOT NULL,
productid INT NOT NULL,
unitprice MONEY NOT NULL
CONSTRAINT DFT_OrderDetails_unitprice DEFAULT(0),
qty SMALLINT NOT NULL
CONSTRAINT DFT_OrderDetails_qty DEFAULT(1),
discount NUMERIC(4, 3) NOT NULL
CONSTRAINT DFT_OrderDetails_discount DEFAULT(0),
CONSTRAINT PK_OrderDetails PRIMARY KEY(orderid, productid),
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY(orderid)
REFERENCES dbo.Orders(orderid),
CONSTRAINT CHK_discount CHECK (discount BETWEEN 0 AND 1),
CONSTRAINT CHK_qty CHECK (qty > 0),
CONSTRAINT CHK_unitprice CHECK (unitprice >= 0)
);
GO
INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;
INSERT INTO dbo.OrderDetails SELECT * FROM Sales.OrderDetails;
UPDATE statementUPDATE语句The UPDATE statement is a standard statement you can use to update a subset of rows in a table. UPDATE语句是一种标准语句，可用于更新表中的行子集。To identify the subset of rows you need to update, you specify a predicate in a 要确定需要更新的行子集，可以在WHERE clause. WHERE子句中指定谓词。You specify the assignment of values to columns in a 可以在SET clause, separated by commas.SET子句中指定对列的赋值，并用逗号分隔。
For example, the following 例如，以下UPDATE statement increases the discount of all order details for product 51 by 5 percent:UPDATE语句将产品51的所有订单详细信息的折扣提高了5%：
UPDATE dbo.OrderDetails
SET discount = discount + 0.05
WHERE productid = 51;
Of course, you can run a 当然，可以在更新前后使用相同的筛选器运行SELECT statement with the same filter before and after the update to see the changes. SELECT语句来查看更改。Later in this chapter, I'll show you another way to see the changes, by using a clause called 在本章后面，我将向您展示查看更改的另一种方式，使用名为OUTPUT that you can add to modification statements.OUTPUT的子句，您可以将其添加到修改语句中。
T-SQL supports compound assignment operators: += (plus equal), –= (minus equal), *= (multiplication equal), /= (division equal), %= (modulo equal), and others. T-SQL支持复合赋值运算符：+=（加号等于）、-=（减号等于）、*=（乘法等于）、/=（除法等于）、%=（模等于）等。You can use these operators to shorten assignment expressions such as the one in the preceding query. 可以使用这些运算符缩短赋值表达式，如前面查询中的表达式。Instead of the expression 您可以使用以下表达式来代替表达式discount = discount + 0.05, you can use this expression: discount += 0.05. discount = discount + 0.05。The full 完整的UPDATE statement looks like this:UPDATE语句如下所示：
UPDATE dbo.OrderDetails
SET discount += 0.05
WHERE productid = 51;
All-at-once operations are an important aspect of SQL you should keep in mind when writing 在编写UPDATE statements. UPDATE语句时，所有一次性操作都是SQL的一个重要方面。I explained the concept in Chapter 2, “Single-table queries,” in the context of 我在第2章“单表查询”中解释了SELECT statements, but it's just as applicable with UPDATE statements. SELECT语句的概念，但它同样适用于UPDATE语句。Remember that all expressions that appear in the same logical phase are evaluated as a set, logically at the same point in time. 请记住，在同一逻辑阶段出现的所有表达式都作为一个集合在同一时间点进行逻辑计算。Consider the following 考虑下面的UPDATE statement:UPDATE语句：
UPDATE dbo.T1
SET col1 = col1 + 10, col2 = col1 + 10;
Suppose one row in the table has the value 100 in 假设在更新之前，表中的一行在col1 prior to the update. col1中的值为100。Can you determine the values of 更新后，您能确定该行中col1 and col2 in that row after the update?col1和col2的值吗？
If you do not consider the all-at-once concept, you would think that 如果你不考虑一次概念，你会认为col1 will be set to 110 and col2 to 120, as if the assignments were performed from left to right. col1将被设置为110和col2被设置为120，就好像从左到右执行任务一样。However, the assignments take place all at once, meaning that both assignments use the same value of 但是，赋值同时进行，这意味着两个赋值都使用相同的col1—the value before the update. col1值，即更新前的值。The result of this update is that both 此更新的结果是col1 and col2 will end up with the value 110.col1和col2都将以110值结束。
With the concept of all-at-once in mind, can you figure out how to write an 考虑到all at once的概念，您能想出如何编写一个UPDATE statement that swaps the values in the columns col1 and col2? UPDATE语句来交换col1和col2列中的值吗？In most programming languages where expressions and assignments are evaluated in some order (typically left to right), you need a temporary variable. 在大多数以某种顺序（通常从左到右）计算表达式和赋值的编程语言中，需要一个临时变量。However, because in SQL all assignments take place as if they happen at the same point in time, the solution is simple:然而，因为在SQL中，所有赋值都好像发生在同一时间点上，所以解决方案很简单：
UPDATE dbo.T1
SET col1 = col2, col2 = col1;
In both assignments, the source column values used are those prior to the update, so you don't need a temporary variable.在这两种赋值中，使用的源列值都是更新之前的值，因此不需要临时变量。
UPDATE based on a joinUPDATESimilar to the 与DELETE statement, the UPDATE statement also supports a nonstandard form based on joins. DELETE语句类似，UPDATE语句也支持基于联接的非标准表单。As with 与DELETE statements, the join serves a filtering purpose as well as giving you access to attributes from the joined tables.DELETE语句一样，联接起到筛选的作用，并允许您从联接的表中访问属性。
The syntax is similar to a 语法类似于基于连接的SELECT statement based on a join; that is, the FROM and WHERE clauses are the same, but instead of the SELECT clause, you specify an UPDATE clause. SELECT语句；也就是说，FROM子句和WHERE子句是相同的，但指定的不是SELECT子句，而是UPDATE子句。The UPDATE keyword is followed by the alias of the table that is the target of the update (you can't update more than one table in the same statement), followed by the SET clause with the column assignments.UPDATE关键字后面是作为更新目标的表的别名（不能在同一语句中更新多个表），后面是带有列分配的SET子句。
For example, the 例如，Listing 8-1中的UPDATE statement in Listing 8-1 increases the discount of all order details of orders placed by customer 1 by 5 percent.UPDATE语句将客户1下订单的所有订单详细信息的折扣提高了5%。
LISTING 8-1 UPDATE based on a joinUPDATE
UPDATE OD
SET discount += 0.05
FROM dbo.OrderDetails AS OD
INNER JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1;
In terms of logical processing, you start with the 在逻辑处理方面，从FROM clause, move on to the WHERE clause, and finally go to the UPDATE clause. FROM子句开始，转到WHERE子句，最后转到UPDATE子句。The query joins the 该查询基于订单详细信息的订单ID和订单ID之间的匹配，将OrderDetails table (aliased as OD) with the Orders table (aliased as O) based on a match between the order detail's order ID and the order's order ID. OrderDetails表（别名为OD）与Orders表（别名为O）连接起来。The query then filters only the rows where the order's customer ID is 然后，查询只筛选订单的客户ID为1. 1的行。The query then specifies in the 然后，查询在UPDATE clause that OD (the alias of the OrderDetails table) is the target of the update, and it increases the discount by 5 percent. UPDATE子句中指定OD（OrderDetails表的别名）是更新的目标，并将折扣增加5%。You can also specify the full table name in the 如果愿意，还可以在UPDATE clause if you like.UPDATE子句中指定完整的表名。
If you want to achieve the same task by using standard code, you can use a subquery instead of a join, like this:如果希望通过使用标准代码实现相同的任务，可以使用子查询而不是联接，如下所示：
UPDATE dbo.OrderDetails
SET discount += 0.05
WHERE EXISTS
(SELECT * FROM dbo.Orders AS O
WHERE O.orderid = OrderDetails.orderid
AND O.custid = 1);
The query's 查询的WHERE clause filters only order details in which a related order is placed by customer 1. WHERE子句只筛选客户1下的相关订单的订单详细信息。With this particular task, SQL Server processes both versions the same way (using the same query plan); therefore, you shouldn't expect performance differences between the two. 对于这个特定任务，SQL Server以相同的方式处理两个版本（使用相同的查询计划）；因此，您不应该期望两者之间存在性能差异。As I mentioned earlier, I recommend sticking to standard code unless you have a compelling reason to do otherwise.正如我前面提到的，我建议您坚持使用标准代码，除非您有令人信服的理由这样做。
There are cases where the join version has advantages. 在某些情况下，join版本具有优势。In addition to filtering, the join also gives you access to attributes from other tables you can use in the column assignments in the 除了筛选之外，联接还允许您访问其他表中的属性，这些表可以在SET clause. SET子句的列指定中使用。The same access to the joined table is used for both filtering and assignment purposes. 对联接表的相同访问用于筛选和分配目的。However, with the subquery approach, you need separate subqueries for filtering and assignments; plus, you need a separate subquery for each assignment. 但是，使用子查询方法时，需要单独的子查询来进行筛选和分配；另外，每个作业都需要一个单独的子查询。In SQL Server, each subquery involves separate access to the other table.在SQL Server中，每个子查询都涉及对另一个表的单独访问。
For example, consider the following nonstandard 例如，考虑以下基于连接的非标准UPDATE statement based on a join:UPDATE语句：
UPDATE T1
SET col1 = T2.col1,
col2 = T2.col2,
col3 = T2.col3
FROM dbo.T1 JOIN dbo.T2
ON T2.keycol = T1.keycol
WHERE T2.col4 = 'ABC';
This statement joins the tables 此语句基于T1 and T2 based on a match between T1.keycol and T2.keycol. T1.keycol和T2.keycol之间的匹配来连接表T1和T2。The WHERE clause filters only rows where T2.col4 is equal to 'ABC'. WHERE子句只筛选T2.col4等于'ABC'的行。The UPDATE statement marks the T1 table as the target for the UPDATE, and the SET clause sets the values of the columns col1, col2, and col3 in T1 to the values of the corresponding columns from T2.UPDATE语句将T1表标记为UPDATE的目标，SET子句将T1中col1、col2和col3列的值设置为T2中相应列的值。
An attempt to express this task by using standard code with subqueries yields the following lengthy query:试图通过使用标准代码和子查询来表达此任务，会产生以下冗长的查询：
UPDATE dbo.T1
SET col1 = (SELECT col1
FROM dbo.T2
WHERE T2.keycol = T1.keycol),
col2 = (SELECT col2
FROM dbo.T2
WHERE T2.keycol = T1.keycol),
col3 = (SELECT col3
FROM dbo.T2
WHERE T2.keycol = T1.keycol)
WHERE EXISTS
(SELECT *
FROM dbo.T2
WHERE T2.keycol = T1.keycol
AND T2.col4 = 'ABC');
Not only is this version convoluted, but each subquery involves separate access to table 这个版本不仅复杂，而且每个子查询都涉及对表T2. T2的单独访问。So this version is less efficient than the join version.因此，这个版本的效率低于联接版本。
Standard SQL has support for 标准SQL支持只在T-SQL中部分实现的“行构造函数”（也称为“向量表达式”）。row constructors (also known as vector expressions) that were only implemented partially in T-SQL. As of SQL Server 2016, many aspects of row constructors have not yet been implemented, including the ability to use them in the 截至SQL Server 2016，行构造函数的许多方面尚未实现，包括在SET clause of an UPDATE statement like this:UPDATE语句的SET子句中使用它们的能力，如下所示：
UPDATE dbo.T1
SET (col1, col2, col3) =
(SELECT col1, col2, col3
FROM dbo.T2
WHERE T2.keycol = T1.keycol)
WHERE EXISTS
(SELECT *
FROM dbo.T2
WHERE T2.keycol = T1.keycol
AND T2.col4 = 'ABC');
But as you can see, this version would still be more complicated than the join version, because it requires separate subqueries for the filtering part and for obtaining the attributes from the other table for the assignments.但是正如您所看到的，这个版本仍然比连接版本更复杂，因为它需要对筛选部分和从另一个表中获取分配属性进行单独的子查询。
UPDATEUPDATET-SQL supports a proprietary T-SQL支持一种专有的UPDATE syntax that both updates data in a table and assigns values to variables at the same time. UPDATE语法，既可以更新表中的数据，也可以同时为变量赋值。This syntax saves you the need to use separate 这种语法使您无需使用单独的UPDATE and SELECT statements to achieve the same task.UPDATE和SELECT语句来完成相同的任务。
One of the common cases for which you can use this syntax is in maintaining a custom sequence/autonumbering mechanism when the identity column property and the sequence object don't work for you. 可以使用此语法的常见情况之一是，当标识列属性和序列对象不适合您时，维护自定义序列/自动编号机制。One example is when you need to guarantee that there are no gaps between the values. 一个例子是，当您需要保证值之间没有间隙时。To achieve this, you keep the last-used value in a table, and whenever you need a new value, you use the special 为了实现这一点，您需要在表中保留最后使用的值，并且每当您需要一个新值时，您都可以使用特殊的UPDATE syntax to both increment the value in the table and assign it to a variable.UPDATE语法来增加表中的值，并将其分配给变量。
Run the following code to first create the 运行以下代码，首先用列MySequences table with the column val, and then populate it with a single row with the value 0—one less than the first value you want to use:val创建MySequences表，然后用一行填充该表，该行的值比要使用的第一个值小1，即0：
DROP TABLE IF EXISTS dbo.MySequences;
CREATE TABLE dbo.MySequences
(
id VARCHAR(10) NOT NULL
CONSTRAINT PK_MySequences PRIMARY KEY(id),
val INT NOT NULL
);
INSERT INTO dbo.MySequences VALUES('SEQ1', 0);
Now, whenever you need to obtain a new sequence value, use the following code:现在，无论何时需要获取新的序列值，请使用以下代码：
DECLARE @nextval AS INT;
UPDATE dbo.MySequences
SET @nextval = val += 1
WHERE id = 'SEQ1';
SELECT @nextval;
The code declares a local variable called 代码声明了一个名为@nextval. @nextval的局部变量。Then it uses the special 然后，它使用特殊的UPDATE syntax to increment the column value by 1 and assigns the new value to a variable. UPDATE语法将列值增加1，并将新值分配给变量。The code then presents the value in the variable. 然后，代码会显示变量中的值。First 首先将val is set to val + 1, and then the result (val + 1) is set to the variable @nextval.val设置为val+1，然后将结果（val+1）设置为变量@nextval。
The specialized 专门的UPDATE syntax is run as a transaction, and it's more efficient than using separate UPDATE and SELECT statements because it accesses the data only once. UPDATE语法作为事务运行，它比使用单独的UPDATE和SELECT语句更高效，因为它只访问数据一次。Note that variable assignment isn't transactional, though.但请注意，变量赋值不是事务性的。
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TABLE IF EXISTS dbo.MySequences;
T-SQL supports a statement called T-SQL支持一个名为MERGE you can use to merge data from a source into a target, applying different actions (INSERT, UPDATE, and DELETE) based on conditional logic. MERGE的语句，您可以使用它将数据从源合并到目标，并根据条件逻辑应用不同的操作（INSERT、UPDATE和DELETE）。The MERGE statement is part of the SQL standard, although the T-SQL version adds a few nonstandard extensions.MERGE语句是SQL标准的一部分，尽管T-SQL版本添加了一些非标准扩展。
A task achieved by a single 一条MERGE statement typically translates to a combination of several other DML statements (INSERT, UPDATE, and DELETE) without MERGE.MERGE语句完成的任务通常会转换为几个其他DML语句（INSERT、UPDATE和DELETE）的组合，而无需合并。
To demonstrate the 为了演示MERGE statement, I'll use tables called dbo.Customers and dbo.CustomersStage. MERGE语句，我将使用名为dbo.Customers和dbo.CustomersStage的表。Run Listing 8-2 to create those tables and populate them with sample data.运行Listing 8-2来创建这些表，并用示例数据填充它们。
LISTING 8-2 Code that creates and populates 创建和填充Customers and CustomersStageCustomers和CustomerStage的代码
DROP TABLE IF EXISTS dbo.Customers, dbo.CustomersStage;
GO
CREATE TABLE dbo.Customers
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
CONSTRAINT PK_Customers PRIMARY KEY(custid)
);
INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');
CREATE TABLE dbo.CustomersStage
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)
);
INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)
VALUES
(2, 'AAAAA', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(5, 'BBBBB', 'CCCCC', 'DDDDD'),
(6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
(7, 'cust 7 (new)', '(777) 777-7777', 'address 7');
Run the following query to examine the contents of the 运行以下查询以检查Customers table:Customers表的内容：
SELECT * FROM dbo.Customers;
This query returns the following output:此查询返回以下输出：
custid companyname phone address
----------- ---------------- -------------------- ------------
1 cust 1 (111) 111-1111 address 1
2 cust 2 (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
4 cust 4 (444) 444-4444 address 4
5 cust 5 (555) 555-5555 address 5
Run the following query to examine the contents of the 运行以下查询以检查CustomersStage table:CustomerStage表的内容：
SELECT * FROM dbo.CustomersStage;
This query returns the following output:此查询返回以下输出：
custid companyname phone address
----------- ---------------- -------------------- ------------
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB CCCCC DDDDD
6 cust 6 (new) (666) 666-6666 address 6
7 cust 7 (new) (777) 777-7777 address 7
Suppose you need to merge the contents of the 假设需要将CustomersStage table (the source) into the Customers table (the target). CustomerStage表（源）的内容合并到Customers表（目标）中。More specifically, you need to add customers that do not exist and update the customers that do exist.更具体地说，您需要添加不存在的客户，并更新确实存在的客户。
If you already feel comfortable with deletions and updates based on joins, you should feel quite comfortable with 如果您已经对基于连接的删除和更新感到满意，那么您应该对MERGE because it's based on join semantics. MERGE感到满意，因为它基于连接语义。You specify the target table name in the 在MERGE clause and the source table name in the USING clause. MERGE子句中指定目标表名，在USING子句中指定源表名。You define a merge condition by specifying a predicate in the 通过在ON clause. ON子句中指定谓词来定义合并条件。The merge condition defines which rows in the source table have matches in the target and which don't. 合并条件定义源表中哪些行与目标中的行匹配，哪些行不匹配。You define the action to take when a match is found in a clause called 定义在名为WHEN MATCHED THEN, and the action to take when a match is not found in the WHEN NOT MATCHED THEN clause.WHEN MATCHED THEN的子句中找到匹配项时要执行的操作，以及在WHEN NOT MATCHED THEN子句中找不到匹配项时要执行的操作。
Here's the first example for the 下面是MERGE statement. MERGE语句的第一个示例。It adds nonexistent customers and updates existing ones:它会添加不存在的客户并更新现有客户：
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);
Note
It's mandatory to terminate the 必须用分号终止MERGE statement with a semicolon, whereas in most other statements in T-SQL, this is optional. MERGE语句，而在T-SQL中的大多数其他语句中，这是可选的。As mentioned, it's a best practice to terminate all statements even when you're not required to.如前所述，最好的做法是终止所有声明，即使不需要这样做。
This 这个MERGE statement defines the Customers table as the target (in the MERGE clause) and the CustomersStage table as the source (in the USING clause). MERGE语句将Customers表定义为目标（在MERGE子句中），将CustomersStage表定义为源（在USING子句中）。Notice that you can assign aliases to the target and source tables for brevity (TGT and SRC in this case). 注意，为了简洁起见，可以为目标表和源表分配别名（本例中为TGT和SRC）。The predicate 谓词TGT.custid = SRC.custid is used to define what is considered a match and what is considered a nonmatch. TGT.custid = SRC.custid用于定义什么是匹配的，什么是不匹配的。In this case, if a customer ID that exists in the source also exists in the target, that's a match. 在这种情况下，如果源中存在的客户ID在目标中也存在，那么这就是匹配。If a customer ID in the source does not exist in the target, that's a nonmatch.如果源中的客户ID在目标中不存在，则为不匹配。
This 当找到匹配项时，此MERGE statement defines an UPDATE action when a match is found, setting the target companyname, phone, and address values to those of the corresponding row from the source. MERGE语句定义更新操作，将目标companyname、phone和address值设置为源中相应行的值。Notice that the syntax of the 请注意，UPDATE action is similar to a normal UPDATE statement, except that you don't need to provide the name of the table that is the target of the update because it was already defined in the MERGE INTO clause.UPDATE操作的语法与普通UPDATE语句类似，只是不需要提供作为更新目标的表的名称，因为它已在MERGE INTO子句中定义。
This 此MERGE statement defines an INSERT action when a match is not found, inserting the row from the source to the target. MERGE语句定义了在未找到匹配项时的INSERT操作，即将行从源插入到目标。Again, the syntax of the 同样，INSERT action is similar to a normal INSERT statement, except that you don't need to provide the name of the target table because it was already defined in the MERGE INTO clause.INSERT操作的语法类似于普通的INSERT语句，只是不需要提供目标表的名称，因为它已经在MERGE INTO子句中定义。
The MERGE statement reports that five rows were modified:MERGE语句报告修改了五行：
(5 row(s) affected)
This includes three rows that were updated (customers 2, 3, and 5) and two that were inserted (customers 6 and 7). 这包括已更新的三行（客户2、3和5）和已插入的两行（客户6和7）。Query the 查询Customers table to get the new contents:Customers表以获取新内容：
SELECT * FROM dbo.Customers;
This query returns the following output:此查询返回以下输出：
custid companyname phone address
----------- ------------------- -------------------- ----------
1 cust 1 (111) 111-1111 address 1
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
4 cust 4 (444) 444-4444 address 4
5 BBBBB CCCCC DDDDD
6 cust 6 (new) (666) 666-6666 address 6
7 cust 7 (new) (777) 777-7777 address 7
The WHEN MATCHED clause defines what action to take against the target when a source row is matched by a target row. WHEN MATCHED子句定义当源行与目标行匹配时，对目标行采取的操作。The WHEN NOT MATCHED clause defines what action to take against the target when a source row is not matched by a target row. WHEN NOT MATCHED子句定义了当源行与目标行不匹配时对目标行采取的操作。T-SQL also supports a third clause that defines what action to take when a target row is not matched by a source row; this clause is called T-SQL还支持第三个子句WHEN NOT MATCHED BY SOURCE. WHEN NOT MATCHED BY SOURCE，它定义了当目标行与源行不匹配时要采取的操作；当源不匹配时调用此子句。For example, suppose you want to add logic to the 例如，假设您希望向MERGE example to delete rows from the target when there's no matching source row. MERGE示例添加逻辑，以便在没有匹配的源行时从目标中删除行。To achieve this, add the 要实现这一点，请使用WHEN NOT MATCHED BY SOURCE clause with a DELETE action, like this:DELETE操作添加WHEN NOT MATCHED BY SOURCE子句，如下所示：
MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Query the 查询Customers table to see the result of this MERGE statement:Customers表以查看此MERGE语句的结果：
SELECT * FROM dbo.Customers;
This query returns the following output, showing that customers 1 and 4 were deleted:此查询返回以下输出，显示客户1和客户4已被删除：
custid companyname phone address
----------- ------------------- -------------------- ----------
2 AAAAA (222) 222-2222 address 2
3 cust 3 (333) 333-3333 address 3
5 BBBBB CCCCC DDDDD
6 cust 6 (new) (666) 666-6666 address 6
7 cust 7 (new) (777) 777-7777 address 7
Going back to the first 回到第一个MERGE example, which updates existing customers and adds nonexistent ones, you can see that it doesn't check whether column values are actually different before applying an update. MERGE示例，它更新现有客户并添加不存在的客户，您可以看到，在应用更新之前，它不会检查列值是否实际不同。This means that a customer row is modified even when the source and target rows are identical. 这意味着即使源行和目标行相同，也会修改客户行。If you want to apply the update only if at least one column value is different, there is a way to achieve this.如果只想在至少一个列值不同时应用更新，有一种方法可以实现这一点。
The MERGE statement supports adding a predicate to the different action clauses by using the AND option; the action will take place only if the additional predicate evaluates to TRUE. MERGE语句支持使用AND选项向不同的操作子句添加谓词；只有当附加谓词的计算结果为TRUE时，才会执行该操作。In this case, you need to add a predicate under the 在这种情况下，需要在WHEN MATCHED AND clause that checks that at least one of the column values is different to justify the UPDATE action. WHEN MATCHED AND子句下添加一个谓词，以检查至少一个列值是否不同，从而证明更新操作是正确的。The complete 完整的MERGE statement looks like this:MERGE语句如下所示：
MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED AND
( TGT.companyname <> SRC.companyname
OR TGT.phone <> SRC.phone
OR TGT.address <> SRC.address) THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address);
As you can see, the 如您所见，MERGE statement is powerful, allowing you to express complex modification logic in a single statement.MERGE语句功能强大，允许您在一条语句中表达复杂的修改逻辑。
T-SQL doesn't limit the actions against table expressions to T-SQL没有将针对表表达式的操作限制为仅SELECT only; it also allows other DML statements (INSERT, UPDATE, DELETE, and MERGE) against those. SELECT；它还允许其他DML语句（INSERT、UPDATE、DELETE和MERGE）与这些语句相对。Think about it: as explained in Chapter 5, a table expression doesn't really contain data—it's a reflection of data in underlying tables. 想一想：正如第5章所解释的，表表达式实际上并不包含数据，而是底层表中数据的反映。With this in mind, think of a modification against a table expression as modifying the data in the underlying tables through the table expression. 考虑到这一点，可以将对表表达式的修改视为通过表表达式修改基础表中的数据。Just as with a 正如针对表表达式的SELECT statement against a table expression, a modification statement against a table expression also gets expanded, so in practice the activity is done against the underlying tables.SELECT语句一样，针对表表达式的修改语句也会得到扩展，因此在实践中，活动是针对基础表进行的。
Modifying data through table expressions has a few restrictions:通过表表达式修改数据有几个限制：
If the query defining the table expression joins tables, you're allowed to affect only one of the sides of the join, not both, in the same modification statement.如果定义表表达式的查询联接表，那么在同一个修改语句中，只允许影响联接的一个边，而不允许同时影响两个边。
You cannot update a column that is a result of a calculation; SQL Server doesn't try to reverse-engineer the values.不能更新作为计算结果的列；SQL Server不会尝试对这些值进行反向工程。
INSERT statements must specify values for any columns in the underlying table that do not get their values implicitly. INSERT语句必须为基础表中未隐式获取其值的任何列指定值。Examples for cases where a column can get a value implicitly include a column that allows 列可以隐式获取值的示例包括允许NULLs, has a default value, has an identity property, or is typed as ROWVERSION.NULL、具有默认值、具有标识属性或类型为ROWVERSION的列。
You can find other requirements in SQL Server Books Online.您可以在SQL Server联机丛书中找到其他要求。
One use case for modifying data through table expressions is for better debugging and troubleshooting capabilities. 通过表表达式修改数据的一个用例是更好的调试和故障排除功能。For example, Listing 8-1 contained the following 例如，Listing 8-1包含以下更新语句：UPDATE statement:
UPDATE OD
SET discount += 0.05
FROM dbo.OrderDetails AS OD
INNER JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1;
Suppose, for troubleshooting purposes, you first want to see which rows would be modified by this statement without actually modifying them. 假设，出于故障排除的目的，您首先希望看到该语句将修改哪些行，而不实际修改它们。One option is to revise the code to a 一个选项是将代码修改为SELECT statement, and after troubleshooting the code, change it back to an UPDATE statement. SELECT语句，并在对代码进行故障排除后，将其更改回UPDATE语句。But instead of needing to make such revisions, you define a table expression based on a 但是，您不需要进行这样的修改，而是基于带有联接查询的SELECT statement with the join query and issue an UPDATE statement against the table expression. SELECT语句定义一个表表达式，并针对该表表达式发出UPDATE语句。The following example uses a CTE:以下示例使用CTE：
WITH C AS
(
SELECT custid, OD.orderid,
productid, discount, discount + 0.05 AS newdiscount
FROM dbo.OrderDetails AS OD
INNER JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1
)
UPDATE C
SET discount = newdiscount;
And here's an example using a derived table:下面是一个使用派生表的示例：
UPDATE D
SET discount = newdiscount
FROM ( SELECT custid, OD.orderid,
productid, discount, discount + 0.05 AS newdiscount
FROM dbo.OrderDetails AS OD
INNER JOIN dbo.Orders AS O
ON OD.orderid = O.orderid
WHERE O.custid = 1 ) AS D;
With the table expression, troubleshooting is simpler because you can always highlight just the inner 使用表表达式，故障排除更简单，因为您可以始终只突出显示内部SELECT statement and run it without making any data changes. SELECT语句，并在不进行任何数据更改的情况下运行它。With this example, the use of table expressions is for convenience. 在本例中，使用表表达式是为了方便。However, in some cases using a table expression is the only option. 然而，在某些情况下，使用表表达式是唯一的选择。To demonstrate such a case, I'll use a table called 为了演示这种情况，我将使用一个名为T1 that you create and populate by running the following code:T1的表，您可以通过运行以下代码来创建和填充该表：
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1(col1 INT, col2 INT);
GO
INSERT INTO dbo.T1(col1) VALUES(20),(10),(30);
SELECT * FROM dbo.T1;
This code generates the following output:此代码生成以下输出：
col1 col2
----------- -----------
20 NULL
10 NULL
30 NULL
Suppose you want to update the table, setting 假设要更新表，将col2 to the result of an expression with the ROW_NUMBER function. col2设置为带有ROW_NUMBER函数的表达式的结果。The problem is that the 问题是ROW_NUMBER function is not allowed in the SET clause of an UPDATE statement. UPDATE语句的SET子句中不允许使用ROW_NUMBER函数。Try running the following code:尝试运行以下代码：
UPDATE dbo.T1
SET col2 = ROW_NUMBER() OVER(ORDER BY col1);
You get the following error:出现以下错误：
Msg 4108, Level 15, State 1, Line 672
Windowed functions can only appear in the SELECT or ORDER BY clauses.
To get around this problem, define a table expression that returns both the column you need to update (要解决这个问题，请定义一个表表达式，该表达式返回需要更新的列（col2) and a result column based on an expression with the ROW_NUMBER function (call it rownum). col2）和基于带有ROW_NUMBER函数的表达式（称为rownum）的结果列。Use an 对表表达式使用UPDATE statement against the table expression to set col2 to rownum. UPDATE语句将col2设置为rownum。Here's how the code looks when using a CTE:以下是使用CTE时代码的外观：
WITH C AS
(
SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1) AS rownum
FROM dbo.T1
)
UPDATE C
SET col2 = rownum;
Query the table to see the result of the update:查询表以查看更新的结果：
SELECT * FROM dbo.T1;
You get the following output:您将获得以下输出：
col1 col2
----------- -----------
20 2
10 1
30 3
TOP and OFFSET-FETCHTOP和OFFSET-FETCH进行修改T-SQL supports using the T-SQL支持在TOP option directly in INSERT, UPDATE, DELETE, and MERGE statements. INSERT、UPDATE、DELETE和MERGE语句中直接使用TOP选项。When you use the 对此类语句使用TOP option with such statements, SQL Server stops processing the modification as soon as the specified number or percentage of rows is processed. TOP选项时，SQL Server会在处理指定数量或百分比的行后立即停止处理修改。Unfortunately, unlike with the 不幸的是，与SELECT statement, you cannot specify an ORDER BY clause for the TOP filter in modification statements. SELECT语句不同，您不能在修改语句中为TOP筛选器指定ORDER BY子句。Essentially, whichever rows SQL Server happens to access first will be modified.本质上，SQL Server首先访问的任何行都将被修改。
The 不允许在修改中直接使用OFFSET-FETCH filter is not allowed directly in modifications because this filter requires an ORDER BY clause and modification statements don't support one.OFFSET-FETCH筛选器，因为此筛选器需要ORDER BY子句，而修改语句不支持ORDER BY子句。
An example for a typical usage scenario for modifications with 使用TOP is when you have a large modification, such as a large deletion operation, and you want to split it into multiple smaller chunks.TOP进行修改的典型使用场景的一个例子是，当您有一个较大的修改，例如一个较大的删除操作，并且希望将其拆分为多个较小的块时。
I'll demonstrate modifications with 我将使用名为TOP by using a table called dbo.Orders that you create and populate by running the following code:dbo.Orders的表来演示对TOP的修改，您可以通过运行以下代码来创建和填充该表：
DROP TABLE IF EXISTS dbo.OrderDetails, dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
custid INT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL,
requireddate DATE NOT NULL,
shippeddate DATE NULL,
shipperid INT NOT NULL,
freight MONEY NOT NULL
CONSTRAINT DFT_Orders_freight DEFAULT(0),
shipname NVARCHAR(40) NOT NULL,
shipaddress NVARCHAR(60) NOT NULL,
shipcity NVARCHAR(15) NOT NULL,
shipregion NVARCHAR(15) NULL,
shippostalcode NVARCHAR(10) NULL,
shipcountry NVARCHAR(15) NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
GO
INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;
The following example demonstrates the use of a 下面的示例演示如何使用带有DELETE statement with the TOP option to delete 50 rows from the Orders table:TOP选项的DELETE语句从Orders表中删除50行：
DELETE TOP(50) FROM dbo.Orders;
Because the statement doesn't have an 因为语句没有ORDER BY clause, it deletes whichever 50 rows it stumbles into first. ORDER BY子句，所以它会删除它最先进入的50行。Which rows get chosen is a result of physical data layout and optimization choices.选择哪些行是物理数据布局和优化选择的结果。
Similarly, you can use the 类似地，您可以在TOP option with UPDATE and INSERT statements, but again, an ORDER BY is not allowed. UPDATE和INSERT语句中使用TOP选项，但同样，不允许使用ORDER BY。As an example of an 作为带有UPDATE statement with TOP, the following code updates 50 rows from the Orders table, increasing their freight values by 10:TOP的UPDATE语句的一个示例，以下代码更新订单表中的50行，将它们的运费值增加10：
UPDATE TOP (50) dbo.Orders
SET freight += 10.00;
Again, you cannot control which 50 rows will be updated; they are the 50 rows that SQL Server happens to access first.同样，您无法控制更新哪50行；它们是SQL Server碰巧最先访问的50行。
In practice, you typically do care which rows are affected. 实际上，您通常会关心哪些行受到影响。To control this, you can rely on the ability to modify data through table expressions. 要控制这一点，可以依靠通过表表达式修改数据的能力。You define a table expression based on a 您可以基于带有SELECT query with the TOP filter and an ORDER BY clause. TOP筛选器和ORDER BY子句的SELECT查询定义表表达式。You then issue the modification statement against the table expression.然后针对表表达式发出修改语句。
For example, the following code deletes the 50 orders with the lowest order ID values:例如，以下代码删除订单ID值最低的50个订单：
WITH C AS
(
SELECT TOP (50) *
FROM dbo.Orders
ORDER BY orderid
)
DELETE FROM C;
Similarly, the following code updates the 50 orders with the highest order ID values, increasing their 类似地，以下代码使用最高的订单ID值更新50个订单，将其freight values by 10:freight（运费）值增加10：
WITH C AS
(
SELECT TOP (50) *
FROM dbo.Orders
ORDER BY orderid DESC
)
UPDATE C
SET freight += 10.00;
Alternatively, you can use the 或者，可以使用OFFSET-FETCH option instead of TOP, like this:OFFSET-FETCH选项而不是TOP，如下所示：
WITH C AS
(
SELECT *
FROM dbo.Orders
ORDER BY orderid
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
)
DELETE FROM C;
And here's the revised 下面是修订后的UPDATE example:UPDATE示例：
WITH C AS
(
SELECT *
FROM dbo.Orders
ORDER BY orderid DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
)
UPDATE C
SET freight += 10.00;
OUTPUT clauseOUTPUT子句Normally, a modification statement just modifies data. 通常，修改语句只是修改数据。However, sometimes you might find it useful to return information from the modified rows for troubleshooting, auditing, and archiving. 但是，有时您可能会发现，从修改的行返回信息以进行故障排除、审核和归档非常有用。T-SQL supports this capability via a clause called T-SQL通过添加到修改语句中的名为OUTPUT that you add to the modification statement. OUTPUT的子句来支持此功能。In this clause, you specify attributes you want to return from the modified rows.在本子句中，指定要从修改的行返回的属性。
The OUTPUT clause is designed similarly to the SELECT clause, only you need to prefix the attributes with either the inserted or deleted keyword. OUTPUT子句的设计与SELECT子句类似，只需要在属性前面加上inserted或deleted关键字。In an 在INSERT statement, you refer to inserted; in a DELETE statement, you refer to deleted; and in an UPDATE statement, you refer to deleted for the old state of the row and inserted for the new state.INSERT语句中，指的是inserted；在DELETE语句中，您指的是deleted；在UPDATE语句中，您指的是行的旧状态为deleted，新状态为inserted。
The OUTPUT clause returns a result set, much like a SELECT statement does. OUTPUT子句返回一个结果集，就像SELECT语句一样。If you want to direct the result set to a table, add an 如果要将结果集定向到表，请添加一个带有目标表名的INTO clause with the target table name. INTO子句。If you want to return modified rows back to the caller and also direct a copy to a table, specify two 如果要将修改后的行返回给调用者，并将副本定向到表，请指定两个输出子句：一个包含OUTPUT clauses: one with the INTO clause and one without it.INTO子句，另一个不包含INTO子句。
The following sections provide examples of using the 以下各节提供了将OUTPUT clause with the different modification statements.OUTPUT子句与不同的修改语句一起使用的示例。
INSERT with OUTPUTINSERT配合OUTPUTAn example for a use case of the 带有OUTPUT clause with an INSERT statement is when you need to insert a row set into a table with an identity column, and you need to get back all identity values that were generated. INSERT语句的OUTPUT子句的用例的一个例子是，当您需要将行集插入到具有标识列的表中，并且需要返回生成的所有标识值时。The SCOPE_IDENTITY function returns only the very last identity value that was generated—not all those generated by the statement. SCOPE_IDENTITY函数只返回最后生成的标识值，而不是语句生成的所有标识值。The OUTPUT子句使任务变得简单。OUTPUT clause makes the task simple. I'll use a table called 我将使用一个名为T1 to demonstrate the technique. T1的表格来演示这项技术。Run the following code to create the table 运行以下代码创建表T1 with an identity column called keycol and another column called datacol:T1，其中包含一个名为keycol的标识列和另一个名为datacol的列：
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_T1 PRIMARY KEY,
datacol NVARCHAR(40) NOT NULL
);
Suppose you want to insert into 假设您想在T1 the result of a query against the HR.Employees table and return all newly generated identity values. T1中插入针对HR.Employees表的查询结果，并返回所有新生成的标识值。To achieve this, add the 要实现这一点，请将OUTPUT clause to the INSERT statement and specify the attributes you want to return:OUTPUT子句添加到INSERT语句中，并指定要返回的属性：
INSERT INTO dbo.T1(datacol)
OUTPUT inserted.keycol, inserted.datacol
SELECT lastname
FROM HR.Employees
WHERE country = N'USA';
This statement returns the following result set:此语句返回以下结果集：
keycol datacol
----------- ---------
1 Davis
2 Funk
3 Lew
4 Peled
5 Cameron
(5 row(s) affected)
As you can guess, you can use a similar technique to return sequence values generated for an 正如您所猜测的，您可以使用类似的技术，通过INSERT statement by the NEXT VALUE FOR function (either directly or in a default constraint).NEXT VALUE FOR函数（直接或在默认约束中）返回为INSERT语句生成的序列值。
As mentioned, you can also direct the result set into a table. 如前所述，还可以将结果集定向到表中。The table can be a real table, temporary table, or table variable. 该表可以是实表、临时表或表变量。When the result set is stored in the target table, you can manipulate the data by querying that table. 当结果集存储在目标表中时，可以通过查询该表来操作数据。For example, the following code declares a table variable called 例如，下面的代码声明了一个名为@NewRows, inserts another result set into T1, and directs the result set returned by the OUTPUT clause into the table variable. @NewRows的表变量，将另一个结果集插入T1，并将OUTPUT子句返回的结果集定向到表变量中。The code then queries the table variable just to show the data that was stored in it:然后，代码查询表变量，只是为了显示存储在其中的数据：
DECLARE @NewRows TABLE(keycol INT, datacol NVARCHAR(40));
INSERT INTO dbo.T1(datacol)
OUTPUT inserted.keycol, inserted.datacol
INTO @NewRows(keycol, datacol)
SELECT lastname
FROM HR.Employees
WHERE country = N'UK';
SELECT * FROM @NewRows;
This code returns the following output showing the contents of the table variable:此代码返回以下输出，显示表格变量的内容：
keycol datacol
----------- -------------
6 Mortensen
7 Suurs
8 King
9 Doyle
(4 row(s) affected)
DELETE with OUTPUTDELETE配合OUTPUTThe next example demonstrates the use of the 下一个示例演示了OUTPUT clause with a DELETE statement. OUTPUT子句与DELETE语句的用法。First, run the following code to create a copy of the 首先，运行以下代码，从Orders table from the Sales schema in the dbo schema:dbo架构中的Sales架构创建Orders表的副本：
DROP TABLE IF EXISTS dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
custid INT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL,
requireddate DATE NOT NULL,
shippeddate DATE NULL,
shipperid INT NOT NULL,
freight MONEY NOT NULL
CONSTRAINT DFT_Orders_freight DEFAULT(0),
shipname NVARCHAR(40) NOT NULL,
shipaddress NVARCHAR(60) NOT NULL,
shipcity NVARCHAR(15) NOT NULL,
shipregion NVARCHAR(15) NULL,
shippostalcode NVARCHAR(10) NULL,
shipcountry NVARCHAR(15) NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
GO
INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;
The following code deletes all orders that were placed prior to 2016 and, using the 以下代码删除2016年之前下的所有订单，并使用OUTPUT clause, returns attributes from the deleted rows:OUTPUT子句从删除的行返回属性：
DELETE FROM dbo.Orders
OUTPUT
deleted.orderid,
deleted.orderdate,
deleted.empid,
deleted.custid
WHERE orderdate < '20160101';
This 此DELETE statement returns the following result set:DELETE语句返回以下结果集：
orderid orderdate empid custid
----------- ---------- ----------- -----------
10248 2014-07-04 5 85
10249 2014-07-05 6 79
10250 2014-07-08 4 34
10251 2014-07-08 3 84
...
10803 2015-12-30 4 88
10804 2015-12-30 6 72
10805 2015-12-30 2 77
10806 2015-12-31 3 84
10807 2015-12-31 4 27
(560 row(s) affected)
If you want to archive the rows that are deleted, add an 如果要归档已删除的行，请添加INTO clause and specify the archive table name as the target.INTO子句，并将归档表名称指定为目标。
UPDATE with OUTPUTUPDATE配合OUTPUTBy using the 通过在OUTPUT clause with an UPDATE statement, you can refer to both the state of the modified row before the change (by prefixing the attribute names with the deleted keyword) and to the state after the change (by prefixing the attribute names with the inserted keyword). UPDATE语句中使用OUTPUT子句，可以引用更改前修改行的状态（在属性名称前加上deleted关键字）和更改后的状态（在属性名称前加上inserted关键字）。This way, you can return both old and new states of the updated attributes.这样，可以返回更新属性的旧状态和新状态。
Before I demonstrate how to use the 在演示如何在OUTPUT clause in an UPDATE statement, you should first run the following code to create a copy of the Sales.OrderDetails table from the Sales schema in the dbo schema:UPDATE语句中使用OUTPUT子句之前，您应该首先运行以下代码，从dbo架构中的Sales架构创建Sales.OrderDetails表的副本：
DROP TABLE IF EXISTS dbo.OrderDetails;
CREATE TABLE dbo.OrderDetails
(
orderid INT NOT NULL,
productid INT NOT NULL,
unitprice MONEY NOT NULL
CONSTRAINT DFT_OrderDetails_unitprice DEFAULT(0),
qty SMALLINT NOT NULL
CONSTRAINT DFT_OrderDetails_qty DEFAULT(1),
discount NUMERIC(4, 3) NOT NULL
CONSTRAINT DFT_OrderDetails_discount DEFAULT(0),
CONSTRAINT PK_OrderDetails PRIMARY KEY(orderid, productid),
CONSTRAINT CHK_discount CHECK (discount BETWEEN 0 AND 1),
CONSTRAINT CHK_qty CHECK (qty > 0),
CONSTRAINT CHK_unitprice CHECK (unitprice >= 0)
);
GO
INSERT INTO dbo.OrderDetails SELECT * FROM Sales.OrderDetails;
The following 下面的UPDATE statement increases the discount of all order details for product 51 by 5 percent and uses the OUTPUT clause to return the product ID, old discount, and new discount from the modified rows:UPDATE语句将product 51的所有订单详细信息的折扣增加了5%，并使用OUTPUT子句从修改的行返回产品ID、旧折扣和新折扣：
UPDATE dbo.OrderDetails
SET discount += 0.05
OUTPUT
inserted.orderid,
inserted.productid,
deleted.discount AS olddiscount,
inserted.discount AS newdiscount
WHERE productid = 51;
This statement returns the following output:此语句返回以下输出：
orderid productid olddiscount newdiscount
----------- ----------- ------------ ------------
10249 51 0.000 0.050
10250 51 0.150 0.200
10291 51 0.100 0.150
10335 51 0.200 0.250
10362 51 0.000 0.050
10397 51 0.150 0.200
10472 51 0.000 0.050
10484 51 0.000 0.050
10486 51 0.000 0.050
10537 51 0.000 0.050
...
(39 row(s) affected)
MERGE with OUTPUTMERGE配合OUTPUTYou can also use the 您也可以将OUTPUT clause with the MERGE statement, but remember that a single MERGE statement can invoke multiple different DML actions. OUTPUT子句用于MERGE语句，但请记住，一条MERGE语句可以调用多个不同的DML操作。To identify which DML action produced each output row, you can invoke a function called 要确定哪个DML操作生成了每个输出行，可以在$action in the OUTPUT clause, which will return a string representing the action (INSERT, UPDATE, or DELETE).output子句中调用名为$action的函数，该函数将返回表示该操作的字符串（INSERT、UPDATE或DELETE）。
To demonstrate 为了演示MERGE with OUTPUT, I'll use the tables you created earlier in the section “Merging data” by running Listing 8-2. MERGE配合OUTPUT，我将使用前面在“合并数据”一节中创建的表，方法是运行Listing 8-2。(Rerun that code listing to follow the example.) （重新运行该代码列表以遵循示例。）The following code merges the contents of 以下代码将CustomersStage into Customers, updating the attributes of customers who already exist in the target and adding customers who don't:CustomerStage的内容合并到Customers中，更新目标中已经存在的Customers的属性，并添加不存在的Customers：
MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
OUTPUT $action AS theaction, inserted.custid,
deleted.companyname AS oldcompanyname,
inserted.companyname AS newcompanyname,
deleted.phone AS oldphone,
inserted.phone AS newphone,
deleted.address AS oldaddress,
inserted.address AS newaddress;
This 此MERGE statement uses the OUTPUT clause to return the old and new values of the modified rows. MERGE语句使用OUTPUT子句返回修改行的新旧值。Of course, with 当然，对于INSERT actions, there are no old values, so all references to deleted attributes return NULLs. INSERT操作，没有旧值，因此对已删除属性的所有引用都返回空值。The $action function tells you whether an UPDATE action or an INSERT action produced the output row. $action函数告诉您是UPDATE操作还是INSERT操作生成了输出行。Here's the output of this 下面是这个MERGE statement:MERGE语句的输出：
theaction custid oldcompanyname newcompanyname
--------- ------ -------------- --------------
UPDATE 2 cust 2 AAAAA
UPDATE 3 cust 3 cust 3
UPDATE 5 cust 5 BBBBB
INSERT 6 NULL cust 6 (new)
INSERT 7 NULL cust 7 (new)
theaction custid oldphone newphone oldaddress newaddress
--------- ------ -------------- -------------- ---------- ----------
UPDATE 2 (222) 222-2222 (222) 222-2222 address 2 address 2
UPDATE 3 (333) 333-3333 (333) 333-3333 address 3 address 3
UPDATE 5 (555) 555-5555 CCCCC address 5 DDDDD
INSERT 6 NULL (666) 666-6666 NULL address 6
INSERT 7 NULL (777) 777-7777 NULL address 7
(5 row(s) affected)
The OUTPUT clause returns an output row for every modified row. OUTPUT子句为每个修改的行返回一个输出行。But what if you need to direct only a subset of the modified rows to a table, perhaps for auditing purposes? 但是，如果您只需要将修改过的行的一个子集定向到一个表中，可能是出于审计目的，该怎么办？T-SQL supports a feature called T-SQL支持一个名为“嵌套DML”的功能，您可以使用该功能直接在最终目标表中插入所需的全部修改行的子集。nested DML you can use to directly insert into the final target table only the subset of rows you need from the full set of modified rows.
To demonstrate this capability, first create a copy of the 为了演示此功能，首先通过运行以下代码，从Products table from the Production schema in the dbo schema, as well as the dbo.ProductsAudit table, by running the following code:dbo架构中的Production架构以及dbo.ProductsAudit表中创建Products表的副本：
DROP TABLE IF EXISTS dbo.ProductsAudit, dbo.Products;
CREATE TABLE dbo.Products
(
productid INT NOT NULL,
productname NVARCHAR(40) NOT NULL,
supplierid INT NOT NULL,
categoryid INT NOT NULL,
unitprice MONEY NOT NULL
CONSTRAINT DFT_Products_unitprice DEFAULT(0),
discontinued BIT NOT NULL
CONSTRAINT DFT_Products_discontinued DEFAULT(0),
CONSTRAINT PK_Products PRIMARY KEY(productid),
CONSTRAINT CHK_Products_unitprice CHECK(unitprice >= 0)
);
INSERT INTO dbo.Products SELECT * FROM Production.Products;
CREATE TABLE dbo.ProductsAudit
(
LSN INT NOT NULL IDENTITY PRIMARY KEY,
TS DATETIME2 NOT NULL DEFAULT(SYSDATETIME()),
productid INT NOT NULL,
colname SYSNAME NOT NULL,
oldval SQL_VARIANT NOT NULL,
newval SQL_VARIANT NOT NULL
);
Suppose you now need to update all products supplied by supplier 1, increasing their price by 15 percent. 假设您现在需要更新供应商1提供的所有产品，将其价格提高15%。You also need to audit the old and new values of updated products, but only those with an old price that was less than 20 and a new price that is greater than or equal to 20.您还需要审核更新产品的新旧价值，但仅限于旧价格低于20且新价格大于或等于20的产品。
You can achieve this by using nested DML. 可以通过使用嵌套的DML来实现这一点。You write an 使用UPDATE statement with an OUTPUT clause and define a derived table based on the UPDATE statement. OUTPUT子句编写UPDATE语句，并基于UPDATE语句定义派生表。You write an 编写一条INSERT SELECT statement that queries the derived table, filtering only the subset of rows that is needed. Here's the complete solution code:INSERT SELECT语句来查询派生表，只筛选所需的行子集。以下是完整的解决方案代码：
INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
SELECT productid, N'unitprice', oldval, newval
FROM (UPDATE dbo.Products
SET unitprice *= 1.15
OUTPUT
inserted.productid,
deleted.unitprice AS oldval,
inserted.unitprice AS newval
WHERE supplierid = 1) AS D
WHERE oldval < 20.0 AND newval >= 20.0;
Recall earlier discussions in the book about table expressions—the result of one query can be used as input to another. 回想一下书中早些时候关于表表达式的讨论——一个查询的结果可以用作另一个查询的输入。Here, the result of the statement with the 这里，带有OUTPUT clause is used as the input for the outer INSERT SELECT statement.OUTPUT子句的语句的结果被用作外部INSERT SELECT语句的输入。
Run the following code to query the 运行以下代码查询ProductsAudit table:ProductsAudit表：
SELECT * FROM dbo.ProductsAudit;
You get the following output:您将获得以下输出：
LSN TS ProductID ColName OldVal NewVal
--- ------------------------- ----------- ----------- -------- ------
1 2016-02-12 18:56:04.793 1 unitprice 18.00 20.70
2 2016-02-12 18:56:04.793 2 unitprice 19.00 21.85
Three products were updated, but only two were filtered by the outer query; therefore, only those two were written to the audit table.更新了三个产品，但只有两个被外部查询筛选；因此，只有这两个被写入审计表。
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TABLE IF EXISTS dbo.OrderDetails, dbo.ProductsAudit, dbo.Products,
dbo.Orders, dbo.Customers, dbo.T1, dbo.MySequences, dbo.CustomersStage;
In this chapter, I covered various aspects of data modification. 在本章中，我介绍了数据修改的各个方面。I described inserting, updating, deleting, and merging data. 我描述了插入、更新、删除和合并数据。I also discussed identity and sequence, modifying data through table expressions, using 我还讨论了标识和序列、通过表表达式修改数据、使用TOP (and indirectly OFFSET-FETCH) with modification statements, and returning information from modified rows using the OUTPUT clause.TOP（以及间接的OFFSET-FETCH）和修改语句，以及使用OUTPUT子句从修改过的行返回信息。
This section provides exercises so that you can practice the subjects discussed in this chapter. 本节提供练习，以便您练习本章讨论的主题。The database assumed in the exercise is 本练习中假设的数据库是TSQLV4.TSQLV4。
Run the following code to create the 运行以下代码在dbo.Customers table in the TSQLV4 database:TSQLV4数据库中创建dbo.Customers表：
USE TSQLV4;
DROP TABLE IF EXISTS dbo.Customers;
CREATE TABLE dbo.Customers
(
custid INT NOT NULL PRIMARY KEY,
companyname NVARCHAR(40) NOT NULL,
country NVARCHAR(15) NOT NULL,
region NVARCHAR(15) NULL,
city NVARCHAR(15) NOT NULL
);
Insert into the 在dbo.Customers table a row with the following information:dbo.Customers表中插入一行，其中包含以下信息：
custid: 100
companyname: Coho Winery
country: USA
region: WA
city: Redmond
Insert into the 将dbo.Customers table all customers from Sales.Customers who placed orders.Sales.Customers中下订单的所有客户插入dbo.Customers表。
Use a 使用SELECT INTO statement to create and populate the dbo.Orders table with orders from the Sales.Orders table that were placed in the years 2014 through 2016.SELECT INTO语句来创建dbo.Orders表，并用SalesOrders表中2014年至2016年的订单填充该表。
Delete from the 从dbo.Orders table orders that were placed before August 2014. dbo.Orders表中删除2014年8月之前的订单。Use the 使用OUTPUT clause to return the orderid and orderdate values of the deleted orders:OUTPUT子句返回已删除订单的orderid和orderdate值：
Desired output:
orderid orderdate
----------- -----------
10248 2014-07-04
10249 2014-07-05
10250 2014-07-08
10251 2014-07-08
10252 2014-07-09
10253 2014-07-10
10254 2014-07-11
10255 2014-07-12
10256 2014-07-15
10257 2014-07-16
10258 2014-07-17
10259 2014-07-18
10260 2014-07-19
10261 2014-07-19
10262 2014-07-22
10263 2014-07-23
10264 2014-07-24
10265 2014-07-25
10266 2014-07-26
10267 2014-07-29
10268 2014-07-30
10269 2014-07-31
(22 row(s) affected)
Delete from the 从dbo.Orders table orders placed by customers from Brazil.dbo.Orders表中删除来自巴西的客户下的订单。
Run the following query against 对dbo.Customers, and notice that some rows have a NULL in the region column:dbo.Customers运行以下查询，注意有些行的region列中有NULL：
SELECT * FROM dbo.Customers;
The output from this query is as follows:此查询的输出如下所示：
custid companyname country region city
----------- ---------------- --------------- ---------- ---------------
1 Customer NRZBB Germany NULL Berlin
2 Customer MLTDN Mexico NULL México D.F.
3 Customer KBUDE Mexico NULL México D.F.
4 Customer HFBZG UK NULL London
5 Customer HGVLZ Sweden NULL Luleå
6 Customer XHXJV Germany NULL Mannheim
7 Customer QXVLA France NULL Strasbourg
8 Customer QUHWH Spain NULL Madrid
9 Customer RTXGC France NULL Marseille
10 Customer EEALV Canada BC Tsawassen
...
(90 row(s) affected)
Update the 更新dbo.Customers table, and change all NULL region values to <None>. dbo.Customers表，并将所有空区域值更改为<None>。Use the 使用OUTPUT clause to show the custid, oldregion, and newregion:OUTPUT子句显示custid、oldregion和newregion：
Desired output:
custid oldregion newregion
----------- --------------- ---------------
1 NULL <None>
2 NULL <None>
3 NULL <None>
4 NULL <None>
5 NULL <None>
6 NULL <None>
7 NULL <None>
8 NULL <None>
9 NULL <None>
11 NULL <None>
12 NULL <None>
13 NULL <None>
14 NULL <None>
16 NULL <None>
17 NULL <None>
18 NULL <None>
19 NULL <None>
20 NULL <None>
23 NULL <None>
24 NULL <None>
25 NULL <None>
26 NULL <None>
27 NULL <None>
28 NULL <None>
29 NULL <None>
30 NULL <None>
39 NULL <None>
40 NULL <None>
41 NULL <None>
44 NULL <None>
49 NULL <None>
50 NULL <None>
52 NULL <None>
53 NULL <None>
54 NULL <None>
56 NULL <None>
58 NULL <None>
59 NULL <None>
60 NULL <None>
63 NULL <None>
64 NULL <None>
66 NULL <None>
68 NULL <None>
69 NULL <None>
70 NULL <None>
72 NULL <None>
73 NULL <None>
74 NULL <None>
76 NULL <None>
79 NULL <None>
80 NULL <None>
83 NULL <None>
84 NULL <None>
85 NULL <None>
86 NULL <None>
87 NULL <None>
90 NULL <None>
91 NULL <None>
(58 row(s) affected)
Update all orders in the 更新dbo.Orders table that were placed by United Kingdom customers, and set their shipcountry, shipregion, and shipcity values to the country, region, and city values of the corresponding customers.dbo.Orders表中英国客户下的所有订单，并将其shipcountry、shipregion和shipcity值设置为相应客户的country、region和city值。
Run the following code to create the tables 运行以下代码创建Orders and OrderDetails and populate them with data:Orders和OrderDetails表，并用数据填充它们：
USE TSQLV4;
DROP TABLE IF EXISTS dbo.OrderDetails, dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
custid INT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL,
requireddate DATE NOT NULL,
shippeddate DATE NULL,
shipperid INT NOT NULL,
freight MONEY NOT NULL
CONSTRAINT DFT_Orders_freight DEFAULT(0),
shipname NVARCHAR(40) NOT NULL,
shipaddress NVARCHAR(60) NOT NULL,
shipcity NVARCHAR(15) NOT NULL,
shipregion NVARCHAR(15) NULL,
shippostalcode NVARCHAR(10) NULL,
shipcountry NVARCHAR(15) NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
CREATE TABLE dbo.OrderDetails
(
orderid INT NOT NULL,
productid INT NOT NULL,
unitprice MONEY NOT NULL
CONSTRAINT DFT_OrderDetails_unitprice DEFAULT(0),
qty SMALLINT NOT NULL
CONSTRAINT DFT_OrderDetails_qty DEFAULT(1),
discount NUMERIC(4, 3) NOT NULL
CONSTRAINT DFT_OrderDetails_discount DEFAULT(0),
CONSTRAINT PK_OrderDetails PRIMARY KEY(orderid, productid),
CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY(orderid)
REFERENCES dbo.Orders(orderid),
CONSTRAINT CHK_discount CHECK (discount BETWEEN 0 AND 1),
CONSTRAINT CHK_qty CHECK (qty > 0),
CONSTRAINT CHK_unitprice CHECK (unitprice >= 0)
);
GO
INSERT INTO dbo.Orders SELECT * FROM Sales.Orders;
INSERT INTO dbo.OrderDetails SELECT * FROM Sales.OrderDetails;
Write and test the T-SQL code that is required to truncate both tables, and make sure your code runs successfully.编写并测试截断两个表所需的T-SQL代码，并确保代码成功运行。
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TABLE IF EXISTS dbo.OrderDetails, dbo.Orders, dbo.Customers;
This section provides solutions to the preceding exercises.本节为前面的练习提供解决方案。
This exercise is split into three parts. 本练习分为三个部分。The following sections provide the solutions to those parts.以下各节提供了这些部件的解决方案。
Make sure you are connected to the 确保已连接到TSQLV4 database:TSQLV4数据库：
USE TSQLV4;
Use the following 使用以下INSERT VALUES statement to insert a row into the Customers table with the values provided in the exercise:INSERT VALUES语句，使用练习中提供的值将一行插入到Customers表中：
INSERT INTO dbo.Customers(custid, companyname, country, region, city)
VALUES(100, N'Coho Winery', N'USA', N'WA', N'Redmond');
One way to identify customers who placed orders is to use the 识别下订单的客户的一种方法是使用EXISTS predicate, as the following query shows:EXISTS谓词，如下查询所示：
SELECT custid, companyname, country, region, city
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid);
To insert the rows returned from this query into the 要将此查询返回的行插入dbo.Customers table, you can use an INSERT SELECT statement as follows:dbo.Customers表，可以使用INSERT SELECT语句，如下所示：
INSERT INTO dbo.Customers(custid, companyname, country, region, city)
SELECT custid, companyname, country, region, city
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid);
The following code first ensures that the session is connected to the 下面的代码首先确保会话连接到TSQLV4 database, and then it drops the dbo.Orders table if it already exists. TSQLV4数据库，然后删除dbo.Orders表（如果已经存在）。Finally, it uses the 最后，它使用SELECT INTO statement to create a new dbo.Orders table and populate it with orders from the Sales.Orders table placed in the years 2014 through 2016:SELECT INTO语句创建一个新的dbo.Orders表，并用Sales.Orders表中2014年至2016年的订单填充该表：
USE TSQLV4;
DROP TABLE IF EXISTS dbo.Orders;
SELECT *
INTO dbo.Orders
FROM Sales.Orders
WHERE orderdate >= '20140101'
AND orderdate < '20170101';
To delete orders placed before August 2014, you need a 要删除2014年8月之前的订单，您需要一个DELETE statement with a filter based on the predicate orderdate < '20140801'. DELETE语句，该语句带有基于谓词orderdate<'20140801'的筛选器。As requested, use the 根据请求，使用OUTPUT clause to return attributes from the deleted rows:OUTPUT子句从已删除的行返回属性：
DELETE FROM dbo.Orders
OUTPUT deleted.orderid, deleted.orderdate
WHERE orderdate < '20140801';
This exercise requires you to write a 本练习要求您编写一条DELETE statement that deletes rows from one table (dbo.Orders) based on the existence of a matching row in another table (dbo.Customers). DELETE语句，根据另一个表（dbo.Customers）中是否存在匹配的行，从一个表（dbo.Orders）中删除行。One way to handle the task is to use a standard 处理该任务的一种方法是在DELETE statement with an EXISTS predicate in the WHERE clause, like this:WHERE子句中使用带有EXISTS谓词的标准DELETE语句，如下所示：
DELETE FROM dbo.Orders
WHERE EXISTS
(SELECT *
FROM dbo.Customers AS C
WHERE Orders.custid = C.custid
AND C.country = N'Brazil');
This 此DELETE statement deletes the rows from the dbo.Orders table for which a related row exists in the dbo.Customers table with the same customer ID as the order's customer ID and the customer's country is Brazil.DELETE语句删除dbo.Orders表中的行，对于这些行，dbo.Customers表中存在一个相关行，该行的客户ID与订单的客户ID相同，并且客户的国家是巴西。
Another way to handle this task is to use the T-SQL–specific 处理此任务的另一种方法是基于连接使用特定于T-SQL的DELETE syntax based on a join, like this:DELETE语法，如下所示：
DELETE FROM O
FROM dbo.Orders AS O
INNER JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE country = N'Brazil';
The join between the dbo.Orders and dbo.Customers tables serves a filtering purpose. dbo.Orders和dbo.Customers表之间的连接用于筛选目的。The join matches each order with the customer who placed the order. 联接将每个订单与下订单的客户匹配。The WHERE clause filters only rows for which the customer's country is Brazil. WHERE子句只筛选客户所在国家为巴西的行。The DELETE FROM clause refers to the alias O representing the table Orders, indicating that Orders is the target of the DELETE operation.DELETE FROM子句指的是代表表Orders的别名O，表示Orders是DELETE操作的目标。
As a standard alternative, you can use the 作为标准的替代方案，您可以使用MERGE statement to handle this task. MERGE语句来处理此任务。Even though you normally think of using 尽管在需要基于条件逻辑应用不同操作时，通常会考虑使用MERGE when you need to apply different actions based on conditional logic, you also can use it when you need to apply only one action. MERGE，但在只需要应用一个操作时，也可以使用MERGE。In our case, you can use the 在例子中，可以将MERGE statement with the WHEN MATCHED clause alone; you don't need to have a WHEN NOT MATCHED clause as well. MERGE语句单独与WHEN MATCHED子句一起使用；你不需要有一个WHEN NOT MATCHED子句。The following 下面的MERGE statement handles the request in the exercise:MERGE语句处理练习中的请求：
MERGE INTO dbo.Orders AS O
USING (SELECT * FROM dbo.Customers WHERE country = N'Brazil') AS C
ON O.custid = C.custid
WHEN MATCHED THEN DELETE;
This 这个MERGE statement defines the dbo.Orders table as the target. MERGE语句将dbo.Orders表定义为目标。It defines a table expression with customers from the 它定义了一个表表达式，其中dbo.Customers table that are from Brazil as the source. dbo.Customers表中的客户来自巴西。An order is deleted from the target (当在源（dbo.Orders) when a matching row is found in the source (dbo.Customers) with the same customer ID.dbo.Customers）中找到具有相同客户ID的匹配行时，将从目标（dbo.Orders）中删除订单。
This exercise involves writing an 本练习涉及编写一条UPDATE statement that filters only rows for which the region attribute is NULL. UPDATE语句，该语句只筛选区域属性为NULL的行。Make sure you use the 在查找IS NULL predicate and not an equality operator when looking for NULLs. NULL时，请确保使用IS NULL谓词，而不是相等运算符。Use the 使用OUTPUT clause to return the requested information. OUTPUT子句返回请求的信息。Here's the complete 以下是完整的UPDATE statement:UPDATE语句：
UPDATE dbo.Customers
SET region = '<None>'
OUTPUT
deleted.custid,
deleted.region AS oldregion,
inserted.region AS newregion
WHERE region IS NULL;
One way to solve this exercise is to use the T-SQL–specific 解决此问题的一种方法是基于连接使用特定于T-SQL的UPDATE syntax based on a join. UPDATE语法。You can join 您可以根据订单的客户ID和客户的客户ID之间的匹配来加入dbo.Orders and dbo.Customers based on a match between the order's customer ID and the customer's customer ID. dbo.Orders和dbo.Customers。In the 在WHERE clause, you can filter only the rows where the customer's country is the United Kingdom. WHERE子句中，您只能筛选客户所在国家为英国的行。In the 在UPDATE clause, specify the alias you assigned to the dbo.Orders table to indicate that it's the target of the modification. UPDATE子句中，指定分配给dbo.Orders表的别名，以指示它是修改的目标。In the 在SET clause, assign the values of the shipping location attributes of the order to the location attributes of the corresponding customer. SET子句中，将订单的装运位置属性的值分配给相应客户的位置属性。Here's the complete 以下是完整的UPDATE statement:UPDATE语句：
UPDATE O
SET shipcountry = C.country,
shipregion = C.region,
shipcity = C.city
FROM dbo.Orders AS O
INNER JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE C.country = N'UK';
Another solution is to define a CTE based on a 另一种解决方案是基于SELECT query that joins dbo.Orders and dbo.Customers and returns both the target location attributes from dbo.Orders and the source location attributes from dbo.Customers. SELECT查询定义CTE，该查询将dbo.Orders和dbo.Customers连接起来，并从dbo.Orders返回目标位置属性，从dbo.Customers返回源位置属性。The outer query would then be an 然后，外部查询将是一个UPDATE statement modifying the target attributes with the values of the source attributes. UPDATE语句，用源属性的值修改目标属性。Here's the complete solution statement:以下是完整的解决方案声明：
WITH CTE_UPD AS
(
SELECT
O.shipcountry AS ocountry, C.country AS ccountry,
O.shipregion AS oregion, C.region AS cregion,
O.shipcity AS ocity, C.city AS ccity
FROM dbo.Orders AS O
INNER JOIN dbo.Customers AS C
ON O.custid = C.custid
WHERE C.country = N'UK'
)
UPDATE CTE_UPD
SET ocountry = ccountry, oregion = cregion, ocity = ccity;
You can also use the 还可以使用MERGE statement to achieve this task. MERGE语句来完成此任务。As explained earlier, even though in a 如前所述，即使在MERGE statement you usually want to specify both the WHEN MATCHED and WHEN NOT MATCHED clauses, the statement supports specifying only one of the clauses. MERGE语句中，您通常希望同时指定WHEN MATCHED和WHEN NOT MATCHED子句，但该语句只支持指定其中一个子句。Using only a 仅使用带有WHEN MATCHED clause with an UPDATE action, you can write a solution that is logically equivalent to the last two solutions. UPDATE操作的WHEN MATCHED子句，就可以编写逻辑上等同于最后两个解决方案的解决方案。Here's the complete solution statement:以下是完整的解决方案声明：
MERGE INTO dbo.Orders AS O
USING (SELECT * FROM dbo.Customers WHERE country = N'UK') AS C
ON O.custid = C.custid
WHEN MATCHED THEN
UPDATE SET shipcountry = C.country,
shipregion = C.region,
shipcity = C.city;
There's a foreign-key relationship between OrderDetails and Orders. OrderDetails和Orders之间存在外键关系。In such a case, you're allowed to truncate the referencing table, but not the referenced table, even if there are no related rows in the referencing table. 在这种情况下，您可以截断引用表，但不能截断引用表，即使引用表中没有相关行。You will need to drop the foreign-key constraint, truncate the tables, and then re-create the constraint, like this:您需要删除外键约束，截断表，然后重新创建约束，如下所示：
ALTER TABLE dbo.OrderDetails DROP CONSTRAINT FK_OrderDetails_Orders;
TRUNCATE TABLE dbo.OrderDetails;
TRUNCATE TABLE dbo.Orders;
ALTER TABLE dbo.OrderDetails ADD CONSTRAINT FK_OrderDetails_Orders
FOREIGN KEY(orderid) REFERENCES dbo.Orders(orderid);
When you're done, run the following code for cleanup:完成后，运行以下代码进行清理：
DROP TABLE IF EXISTS dbo.OrderDetails, dbo.Orders, dbo.Customers;