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
