Update语句的Output从句结构
一,先看1个列子
declare @i table( id varchar(20), [name] varchar(20), address varchar(50), oldStatus char(1), newStatus char(1))update top (3) dbo.Table_1 set status = 'C' --2,选择前3条数据output deleted.id,deleted.[name],deleted.address, --3,输出数据到临时表中(deleted:原始数据 inserted:更新后的新数据)deleted.status,inserted.statusinto @i where status not in('P') --1, 过滤不为P状态的数据
二,现在再来看看它的语法结构
返回信息,或基于表达式,影响每一行的INSERT,UPDATE或DELETE语句。这些结果可以退回作为确认消息的事情处理应用程序使用,归档,以及其他类似的应用需求。另外,结果可以插入到一个表或表变量。
<OUTPUT_CLAUSE> ::={ [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ] [ OUTPUT <dml_select_list> ]}<dml_select_list> ::={ <column_name> | scalar_expression } [ [AS] column_alias_identifier ] [ ,n ]<column_name> ::={ DELETED | INSERTED | from_table_name } . { * | column_name }
1,@ table_variable
指定一个表变量,返回的行被插入而不是被返回给调用者进入。 @ table_variable前必须声明的INSERT,UPDATE或DELETE语句。
如果没有指定column_list中,表变量必须有列的输出结果相同的数。唯一的例外是身份和计算列,必须跳过。如果column_list中指定,任何省略列必须允许空值,
或分配给他们的默认值。
A,A. Using OUTPUT INTO with a simple INSERT statement
下面的例子中插入一行的ScrapReason表,并使用OUTPUT子句返回声明的结果@ MyTableVar表变量。由于ScrapReasonID列具有IDENTITY属性的定义,一个值没有规定该列INSERT语句。不过请注意,由数据库引擎生成该列的值在列中OUTPUT子句返回INSERTED.ScrapReasonID。
USE AdventureWorks;GODECLARE @MyTableVar table( ScrapReasonID smallint, Name varchar(50), ModifiedDate datetime);INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVarVALUES (N'Operator error', GETDATE());--Display the result set of the table variable.SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;--Display the result set of the table.SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason;GO
B,Using OUTPUT with an INSERT…SELECT statement
下面的示例创建EmployeeSales的表并插入几行,然后把它通过使用SELECT语句来检索表中的数据源。此外,为列,ProjectedSales,价值计算并插入到表中。 OUTPUT子句返回的INSERT语句的结果给调用应用程序。最后的SELECT语句验证新EmployeeSales的表的内容匹配的OUTPUT子句的结果。
USE AdventureWorks ;GOIF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL DROP TABLE dbo.EmployeeSales;GOCREATE TABLE dbo.EmployeeSales( EmployeeID nvarchar(11) NOT NULL, LastName nvarchar(20) NOT NULL, FirstName nvarchar(20) NOT NULL, CurrentSales money NOT NULL, ProjectedSales money NOT NULL);GOINSERT INTO dbo.EmployeeSales OUTPUT INSERTED.EmployeeID, INSERTED.LastName, INSERTED.FirstName, INSERTED.CurrentSales, INSERTED.ProjectedSales SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.10 FROM HumanResources.Employee AS e INNER JOIN Sales.SalesPerson AS sp ON e.EmployeeID = sp.SalesPersonID INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.EmployeeID LIKE '2%' ORDER BY c.LastName, c.FirstName;GOSELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSalesFROM dbo.EmployeeSales;GO
C. Using OUTPUT with a DELETE statement
下面的例子中ShoppingCartItem删除表中的所有行。该条款输出删除.*规定,DELETE语句的结果,这是所有删除的行的列,返回给调用应用程序。 SELECT语句后面验证了删除的ShoppingCartItem表操作的结果。
USE AdventureWorks;GODELETE Sales.ShoppingCartItem OUTPUT DELETED.* ;--Verify all rows in the table have been deleted.SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;GO
D. Using OUTPUT INTO with an UPDATE statement
USE AdventureWorks;GODECLARE @MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDateINTO @MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDateFROM @MyTableVar;GO--Display the result set of the table.--Note that ModifiedDate reflects the value generated by an--AFTER UPDATE trigger.SELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO
E. Using OUTPUT INTO to return an expression
下面的示例建立在定义一个例子,在它们之间的最新VacationHours价值和VacationHours价值的差额OUTPUT子句D表达更新之前应用。这个表达式的值返回到@ MyTableVar列的表中的变量VacationHoursDifference。
USE AdventureWorks;GODECLARE @MyTableVar table( EmpID int NOT NULL, OldVacationHours int, NewVacationHours int, VacationHoursDifference int, ModifiedDate datetime);UPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.VacationHours - DELETED.VacationHours, INSERTED.ModifiedDateINTO @MyTableVar;--Display the result set of the table variable.SELECT EmpID, OldVacationHours, NewVacationHours, VacationHoursDifference, ModifiedDateFROM @MyTableVar;GOSELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO
F. Using OUTPUT INTO with from_table_name in an UPDATE statement
下面的例子,其中更新了WorkOrder表中与指定的ProductID和ScrapReasonID所有工作订单ScrapReasonID列。在输出到子句返回表中的值被更新(WorkOrder),并从产品表。该产品表用于在FROM子句中指定要更新的行。由于WorkOrder表中有一个AFTER UPDATE触发器定义它,INTO关键字是必需的。
USE AdventureWorks;GODECLARE @MyTestVar table ( OldScrapReasonID int NOT NULL, NewScrapReasonID int NOT NULL, WorkOrderID int NOT NULL, ProductID int NOT NULL, ProductName nvarchar(50)NOT NULL);UPDATE Production.WorkOrderSET ScrapReasonID = 4OUTPUT DELETED.ScrapReasonID, INSERTED.ScrapReasonID, INSERTED.WorkOrderID, INSERTED.ProductID, p.Name INTO @MyTestVarFROM Production.WorkOrder AS wo INNER JOIN Production.Product AS p ON wo.ProductID = p.ProductID AND wo.ScrapReasonID= 16 AND p.ProductID = 733;SELECT OldScrapReasonID, NewScrapReasonID, WorkOrderID, ProductID, ProductName FROM @MyTestVar;GO
G. Using OUTPUT INTO with from_table_name in a DELETE statement
下面的示例删除了关于在DELETE语句的FROM子句中定义的搜索标准为基础的ProductProductPhoto表行。 OUTPUT子句返回从表中的列被删除(DELETED.ProductID,DELETED.ProductPhotoID)和列从产品表。使用该表,在FROM子句中指定的行删除。
USE AdventureWorksGODECLARE @MyTableVar table ( ProductID int NOT NULL, ProductName nvarchar(50)NOT NULL, ProductModelID int NOT NULL, PhotoID int NOT NULL);DELETE Production.ProductProductPhotoOUTPUT DELETED.ProductID, p.Name, p.ProductModelID, DELETED.ProductPhotoID INTO @MyTableVarFROM Production.ProductProductPhoto AS phJOIN Production.Product as p ON ph.ProductID = p.ProductID WHERE p.ProductModelID BETWEEN 120 and 130;--Display the results of the table variable.SELECT ProductID, ProductName, ProductModelID, PhotoID FROM @MyTableVarORDER BY ProductModelID;GO
H. Using UPDATE with OPENROWSET to modify a varbinary(max) column
下面的例子将取代现有的形象的varbinary(max存储)的一个新的图像列。 OPENROWSET函数是使用BULK选项的加载到列中的形象。此示例假定名为Tires.jpg在指定的文件路径存在的文件。
USE AdventureWorks;GOUPDATE Production.ProductPhotoSET ThumbNailPhoto = ( SELECT * FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB)AS x )WHERE ProductPhotoID = 1;GO
BULK 选项
--1,SINGLE_BLOB: 返回的内容data_file为单排,单类型为varbinary(max)列集。
--2,SINGLE_CLOB: 通过阅读以ASCII data_file,返回为单排,单类型为varchar(max)使用当前数据库的排序规则,列集的内容。
--3,SINGLE_NLOB:通过阅读为Unicode data_file,返回为单排,单类型为nvarchar(max)使用当前数据库的排序规则,列集的内容。
最新文章
- Docker相关文档
- WCF初探-4:WCF消息交换模式之请求与答复模式
- 跨服务器之间的session共享
- static_cast .xml
- 可学习的多人人脸识别程序(基于Emgu CV)
- 使用PHP导出Word文档的原理和实例
- 聊聊动画引擎 pop
- IOS开发小功能2:二维码扫描界面的设计(横线上下移动)
- [转]linux中颜色的含义
- [日常] HTTP连接管理
- Xamarin AVD x86 问题
- Papers | 超分辨 + 深度学习(未完待续)
- 「THUSCH 2017」大魔法师 解题报告
- (转) How a Kalman filter works, in pictures
- PHP变量的值类型和引用类型
- Map的嵌套使用
- tolua杂记
- Andoird通信之简单聊天ChatApp
- ArcGIS Javascript 图层事件绑定
- 882. Reachable Nodes In Subdivided Graph