--output Demo
--Basic data
if(object_id('dbo.prd','U') is not null) drop table dbo.prd;
CREATE TABLE dbo.prd(
id bigint not null identity(1,1) constraint pk_prd primary key,
code varchar(20) not null,
pname varchar(50) not null,
val decimal(20,8) not null,
note varchar(20) not null default('Empty'),
changelog varchar(max) null
constraint BPK$PRD unique nonclustered (code,pname)
)
insert into prd (code,pname,val) values
('10001','M-AS',10.235),('10002','M-AK',13.25),('10003','M-AL',16.82);
if(object_id('prdUp','U') is not null) drop table prdUp;
SELECT id, code, pname, val+100 as val into prdUp from prd;
------insert output
----直接output
insert into prd (code,pname,val)
output inserted.id,inserted.code,inserted.pname,inserted.val,inserted.note,inserted.changelog
select * from
(values('Test1','M-AS',11),('Test2','M-AK',12),('Test3','M-AL',13)) as D(code,pname,val);
--不在插入字段内的字段(自增的,默认的)也可以被output
----output结果插入中间表(必须为已经存在的表,不同于select into)
--表变量
DECLARE @prdInsert TABLE(
id BIGINT, code VARCHAR(20), pname VARCHAR(50), val DECIMAL(20, 8), note VARCHAR(20), changelog VARCHAR(MAX),
ADDCOLUMN datetime);
--output into
insert into prd (code,pname,val)
output inserted.id,inserted.code,inserted.pname,inserted.val,inserted.note,inserted.changelog,
getdate() ADDCOLUMN --自定义字段
into @prdInsert
select * from
(values('Test4','M-AS',11),('Test5','M-AK',12),('Test6','M-AL',13)) as D(code,pname,val);
--show data
select * from @prdInsert;
/*小结:
output的范围:插入列,被插入表的生成列,自定义列
output into:对象必须是已经存在的表*/
------delete output
--场景:归档删除数据
delete from prd
output deleted.id,deleted.code,deleted.pname
where code like 'Test%';
------update output
--场景:记录数据变化
UPDATE O
SET val = P.val
OUTPUT inserted.id AS id, deleted.val AS oldVal, inserted.val AS newVal
FROM prd O JOIN prdUp P ON O.code = p.code AND o.pname = p.pname
------merge output
select * from prd
select * from
(values('10001','M-AS',100),('10001','M-ZZ',10)) AS D(code,pname,val)
-------------------------简单 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 AdventureWorks
go
--定义一个表格变量
declare @mytablevar table( ScrapReasonID smallint,
Name1 varchar(50),
ModifiedDate datetime)
insert into Production.ScrapReason
output inserted.ScrapReasonID,inserted.[Name],inserted.ModifiedDate into @mytablevar
values ('operator error',getdate());
--显示@mytablevar中的数据
select * from @mytablevar
--显示Production.ScrapReason表中的数据
select * from Production.ScrapReason
go
--将 OUTPUT 用于 INSERT…SELECT 语句
use AdventureWorks
go
if object_id('dbo.EmployeeSales','u') is not null
drop table dbo.EmployeeSales
go
create 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
)
go
insert 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;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
--将 OUTPUT 用于 DELETE 语句
USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* ;
--验证表中所有数据都被删除
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO
--将 OUTPUT INTO 用于 UPDATE
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--显示@MyTableVar的值
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--显示插入表的值
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
-- 使用 OUTPUT INTO 返回表达式
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
VacationHoursDifference int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.VacationHours - DELETED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--显示表变量中的数据
SELECT EmpID, OldVacationHours, NewVacationHours,
VacationHoursDifference, ModifiedDate
FROM @MyTableVar;
GO
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
--在 UPDATE 语句中使用包含 from_table_name 的 OUTPUT INTO
USE AdventureWorks;
GO
DECLARE @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.WorkOrder
SET ScrapReasonID = 4
OUTPUT DELETED.ScrapReasonID,
INSERTED.ScrapReasonID,
INSERTED.WorkOrderID,
INSERTED.ProductID,
p.Name
INTO @MyTestVar
FROM 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 INTO
USE AdventureWorks
GO
DECLARE @MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;
SELECT ProductID, ProductName, ProductModelID, PhotoID
FROM @MyTableVar
ORDER BY ProductModelID;
GO
-- 将 OUTPUT INTO 用于大型对象数据类型
USE AdventureWorks;
GO
DECLARE @MyTableVar table (
DocumentID int NOT NULL,
SummaryBefore nvarchar(max),
SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT INSERTED.DocumentID,
DELETED.DocumentSummary,
INSERTED.DocumentSummary
INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO
-- 在 INSTEAD OF 触发器中使用 OUTPUT
USE AdventureWorks;
GO
IF OBJECT_ID('dbo.vw_ScrapReason','V') IS NOT NULL
DROP VIEW dbo.vw_ScrapReason;
GO
CREATE VIEW dbo.vw_ScrapReason
AS (SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason);
GO
CREATE TRIGGER dbo.io_ScrapReason
ON dbo.vw_ScrapReason
INSTEAD OF INSERT
AS
BEGIN
--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;
END
GO
INSERT vw_ScrapReason (ScrapReasonID, Name, ModifiedDate)
VALUES (99, N'My scrap reason','20030404');
GO
--将 OUTPUT INTO 用于标识列和计算列
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE 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
);
insert into EmployeeSales(LastName,FirstName,CurrentSales)
values('Top','Sa',10)
select * from EmployeeSales
update EmployeeSales set ProjectedSales=1
GO
DECLARE @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, CurrentSales
FROM @MyTableVar;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GO
output Demo
最新推荐文章于 2018-08-16 09:57:03 发布