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
