【SQLSERVER】output用法解析

USE njtestdbGOCREATE TABLE testbzm([ID] bigint IDENTITY(1,1) NOT NULL  PRIMARY KEY,[CODE] varchar(20)  NOT NULL  )insert into testbzm(code) values('10010')select * from testbzm-------------------------简单 INSERT 语句declare @code varchar(20)='10014'declare @t table(id int)if((select count(1) from testbzm where code=@code)=0) begin insert into testbzm(code)  output INSERTED.ID into @t values(@code) --(select '10017' id) 会报错, //select @code 不会报错（没有括号）end select * from @t /* 消息 208，级别 16，状态 0，第 25 行Invalid object name '#t'.*/ --中间结果只能存在表变量中，不能用临时表 ---------------------------------DEMO --将 OUTPUT INTO 用于简单 INSERT 语句use AdventureWorksgo--定义一个表格变量declare @mytablevar table( ScrapReasonID smallint,                           Name1 varchar(50),                           ModifiedDate datetime)insert into Production.ScrapReasonoutput inserted.ScrapReasonID,inserted.[Name],inserted.ModifiedDate into @mytablevarvalues ('operator error',getdate());--显示@mytablevar中的数据select * from @mytablevar--显示Production.ScrapReason表中的数据select * from Production.ScrapReasongo--将 OUTPUT 用于 INSERT…SELECT 语句use AdventureWorksgoif object_id('dbo.EmployeeSales','u') is not nulldrop table dbo.EmployeeSalesgocreate 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.EmployeeSalesoutput inserted.EmployeeID,inserted.LastName,inserted.FirstName,inserted.CurrentSales,inserted.ProjectedSalesSELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.10FROM 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--将 OUTPUT 用于 DELETE 语句USE AdventureWorks;GODELETE Sales.ShoppingCartItem    OUTPUT DELETED.* ;--验证表中所有数据都被删除SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;GO--将 OUTPUT INTO 用于 UPDATEUSE 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;--显示@MyTableVar的值SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDateFROM @MyTableVar;GO--显示插入表的值SELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO-- 使用 OUTPUT INTO 返回表达式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;--显示表变量中的数据SELECT EmpID, OldVacationHours, NewVacationHours,     VacationHoursDifference, ModifiedDateFROM @MyTableVar;GOSELECT TOP (10) EmployeeID, VacationHours, ModifiedDateFROM HumanResources.Employee;GO--在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTOUSE 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--在 DELETE 语句中使用包含 from_table_name 的 OUTPUT INTOUSE 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;SELECT ProductID, ProductName, ProductModelID, PhotoID FROM @MyTableVarORDER BY ProductModelID;GO-- 将 OUTPUT INTO 用于大型对象数据类型USE AdventureWorks;GODECLARE @MyTableVar table (    DocumentID int NOT NULL,    SummaryBefore nvarchar(max),    SummaryAfter nvarchar(max));UPDATE Production.DocumentSET DocumentSummary .WRITE (N'features',28,10)OUTPUT INSERTED.DocumentID,       DELETED.DocumentSummary,        INSERTED.DocumentSummary     INTO @MyTableVarWHERE DocumentID = 3 ;SELECT DocumentID, SummaryBefore, SummaryAfter FROM @MyTableVar;GO-- 在 INSTEAD OF 触发器中使用 OUTPUTUSE AdventureWorks;GOIF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL    DROP VIEW dbo.vw_ScrapReason;GOCREATE VIEW dbo.vw_ScrapReasonAS (SELECT ScrapReasonID, Name, ModifiedDate    FROM Production.ScrapReason);GOCREATE TRIGGER dbo.io_ScrapReason     ON dbo.vw_ScrapReasonINSTEAD OF INSERTASBEGIN--ScrapReasonID is not specified in the list of columns to be inserted --because it is an IDENTITY column.    INSERT INTO Production.ScrapReason (Name, ModifiedDate)        OUTPUT INSERTED.ScrapReasonID, INSERTED.Name,                INSERTED.ModifiedDate    SELECT Name, getdate()    FROM inserted;ENDGOINSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)VALUES (99, N'My scrap reason','20030404');GO--将 OUTPUT INTO 用于标识列和计算列USE AdventureWorks ;GOIF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL    DROP TABLE dbo.EmployeeSales;GOCREATE TABLE dbo.EmployeeSales( EmployeeID   int IDENTITY (1,5)NOT NULL,  LastName     nvarchar(20) NOT NULL,  FirstName    nvarchar(20) NOT NULL,  CurrentSales money NOT NULL,  ProjectedSales AS CurrentSales * 1.10 );GODECLARE @MyTableVar table(  LastName     nvarchar(20) NOT NULL,  FirstName    nvarchar(20) NOT NULL,  CurrentSales money NOT NULL  );INSERT INTO dbo.EmployeeSales (LastName, FirstName, CurrentSales)  OUTPUT INSERTED.LastName,          INSERTED.FirstName,          INSERTED.CurrentSales  INTO @MyTableVar    SELECT c.LastName, c.FirstName, sp.SalesYTD    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;SELECT LastName, FirstName, CurrentSalesFROM @MyTableVar;GOSELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSalesFROM dbo.EmployeeSales;GO