DML_The OUTPUT Clause

CDPJ發表於2020-06-14

DML_The OUTPUT Clause

/**/
----------------------------------------------------------------------------------------------
--帶有OUTPUT的INSERT語句
IF OBJECT_ID('dbo.T5','U') IS NOT NULL DROP TABLE dbo.T5
CREATE TABLE dbo.T5
(
keycol INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_T5 PRIMARY KEY,
datacol NVARCHAR(40) NOT NULL 
);

--查詢一下，可從這個 pubs.employee  表中插入值
SELECT * 
FROM pubs.dbo.employee
WHERE job_id = N'10';

--SET IDENTITY_INSERT dbo.T5 ON;
--dbcc checkident(T1,RESEED,0)   --重置標識列為0
--dbcc checkident(T1,RESEED,1)   --重置標識列為0

INSERT INTO dbo.T5(datacol)
    OUTPUT inserted.keycol, inserted.datacol
    SELECT lname
    FROM pubs.dbo.employee
    WHERE job_id = N'10';

SELECT * FROM T5

dbcc checkident(T5,RESEED,6)   --重置標識列為6，下一個標識值將從 7 開始
--a. 知識點
--在對標識列進行重新規劃相關列值的時候，需採用 checkident函式對錶中標識值進行規整
--1 dbcc checkident(‘表名’) 預設將表中的標識列的起始值初始化為 當前此列的最大值
--   dbcc checkident(‘表名’,NORESEED) 返回當前標識列最大值
--2 dbcc checkident(‘表名’,RESEED) 功能類似 dbcc checkident(‘表名’)
--3 dbcc checkident(‘表名’,RESEED,新標識列起始值) 手動指定新標識列起始值

--b.知識點，再回顧 IDENTITY屬性（Chapter 8. Data Modification: The IDENTITY Property）

--將結果集匯入到另外一個表中
DECLARE @NewRows TABLE(keycol INT, datacol NVARCHAR(40));
INSERT INTO dbo.T5(datacol)
     OUTPUT inserted.keycol, inserted.datacol
     INTO @NewRows
          SELECT lname
          FROM pubs.dbo.employee
          WHERE job_id = N'10';

SELECT * FROM @NewRows;
----------------------------------------------------------------------------------------------
--帶有OUTPUT的DELETE語句

SELECT * FROM dbo.Orders       --儘管 testdb 庫下有 Orders，我們還是從 Northwind.dbo.Orders 插入資料
SELECT * FROM Northwind.dbo.Orders  
WHERE 

IF OBJECT_ID('dbo.OrdersB','U') IS NOT NULL DROP TABLE dbo.OrdersB;
SELECT * INTO dbo.OrdersB FROM Northwind.dbo.Orders  ;

SELECT * FROM testdb.dbo.OrdersB
-------------------------
DELETE FROM dbo.OrdersB
    OUTPUT
         deleted.OrderID,
         deleted.OrderDate,
         deleted.EmployeeID,
         deleted.CustomerID
    WHERE OrderDate < '19980101'

SELECT * FROM dbo.OrdersB
-------------------------

--歸檔
sp_help Northwind.dbo.Orders   ( × )
sp_help dbo.Orders                      ( × )
sp_help Orders

-------------------------
DECLARE @OrdersBB TABLE(OrderID INT, OrderDate datetime, EmployeeID INT, cUSTOMERid NVARCHAR(10))
DELETE FROM dbo.OrdersB
    OUTPUT
         deleted.OrderID,
         deleted.OrderDate,
         deleted.EmployeeID,
         deleted.CustomerID
    INTO @OrdersBB
    WHERE OrderDate < '19980501'

SELECT * FROM @OrdersBB
-------------------------        

----------------------------------------------------------------------------------------------
--帶有OUTPUT的UPDATE語句
--
SELECT * FROM Northwind.dbo.[Order Details];
SELECT * FROM OrderDetails    --檢查一下之前 testdb中是否用過這個表，檢查後沒用過，討厭這個表還要加中括號

IF OBJECT_ID('dbo.OrderDetails','U') IS NOT NULL DROP TABLE dbo.OrderDetails;
SELECT * INTO dbo.OrderDetails FROM Northwind.dbo.[Order Details]

SELECT * FROM OrderDetails  
-------------------------------------
UPDATE dbo.OrderDetails
    SET discount = discount + 0.03
OUTPUT 
    inserted.OrderID,    
    inserted.ProductID,
    deleted.Discount AS olddiscount,
    inserted.Discount AS newdiscount
WHERE ProductID = 51;

------------------------------------

----------------------------------------------------------------------------------------------
--帶有OUTPUT的MERGE語句
--此處使用之前 MERGE中 “合併的例子”
--3.Merge語句 without And condition

--如果不記得欄位值，就查詢一下
SELECT * FROM dbo.Customers

MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
   ON TGT.custid = SRC.custid
WHEN MATCHED THEN       --此處沒有其他條件，即使來源表與目標表完全相同，也要更新目標表
      UPDATE SET
           TGT.companyname = SRC.companyname,
           TGT.phone = SRC.phone,
           TGT.address = SRC.address
WHEN NOT MATCHED THEN
      INSERT (custid, companyname, phone, address)
      VALUES (SRC.custid,SRC.companyname, SRC.phone, SRC.address)  ----->>>此處，去掉MERGE之前結尾的 ; 
OUTPUT $action, inserted.custid,                                   ----->>>$action
deleted.companyname AS oldcompanyname,
inserted.companyname AS newcompanyname,
deleted.phone as oldphone,
inserted.phone as newphone,
deleted.address as oldaddress,
inserted.address as newaddress;


1) --帶有OUTPUT的INSERT語句

   ......（略）

2) --帶有OUTPUT的DELETE語句

......（略）
3) --帶有OUTPUT的UPDATE語句

4) --帶有OUTPUT的MERGE語句

    

 

相關文章