SQL OUTPUT CLUASE

最新推荐文章于 2021-02-13 01:20:14 发布

原创最新推荐文章于 2021-02-13 01:20:14 发布 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#output #sql #microsoft #insert #delete #action

MSSQL 专栏收录该内容

64 篇文章

订阅专栏

本文详细介绍了数据库管理系统的关键操作，包括插入、删除、更新和合并数据，并通过输出语句展示了每一步操作的具体结果，旨在提高数据库操作效率和系统稳定性。

--OUTPUT CLAUSE
--1 INSERT WITH OUTPUT
IF OBJECT_ID('Rows') IS NOT NULL DROP TABLE ROWS
CREATE TABLE [ROWS](COL INT IDENTITY(1,1),DATACOL VARCHAR(20))

INSERT INTO [ROWS]
    OUTPUT inserted.COL,inserted.DATACOL
    SELECT COMPANYNAME FROM Customer
--RESULTS
COL         DATACOL
----------- --------------------
1           Microsoft
2           Intel
3           alibaba
4           siemens
5           Bank
6           GOOGLE

(6 row(s) affected)

-- DELETE WITH OUTPUT
DELETE FROM [ROWS]
       OUTPUT deleted.COL,
              deleted.DATACOL
              WHERE COL<10

-- UPDATE WITH OUTPUT
   UPDATE [ROWS]
   SET DATACOLDATACOL=DATACOL+'--'
   OUTPUT inserted.COL,
          deleted.DATACOL AS OLDDATA,
          inserted.DATACOL AS NEWDATA
   WHERE COL<11
--RESULTS
COL         OLDDATA              NEWDATA
----------- -------------------- --------------------
7           Microsoft            Microsoft--
8           Intel                Intel--
9           alibaba              alibaba--
10          siemens              siemens--

(4 row(s) affected)


--MERGE WITH OUTPUT
MERGE INTO dbo.Customer AS TGT
USING dbo.CustomerStage AS SRC
ON TGT.CUSID=SRC.CUSID
WHEN MATCHED THEN
     UPDATE
     SET TGT.CustomeName=SRC.CustomeName,
         TGT.companyname=SRC.companyname
WHEN NOT MATCHED THEN
     INSERT (cusid,CustomeName,companyname)
     VALUES (SRC.cusid,SRC.CustomeName,SRC.companyname)
OUTPUT $ACTION,
       INSERTED.CUSID,
       DELETED.CustomeName AS OLDCustomeName,
       INSERTED.CustomeName AS NEWCustomeName,
       DELETED.companyname AS OLDcompanyname,
       INSERTED.companyname AS companyname;

$ACTION    CUSID       OLDCustomeName       NEWCustomeName       OLDcompanyname       companyname
---------- ----------- -------------------- -------------------- -------------------- --------------------
UPDATE     1           guo hu               guo hu               wicresoft            Microsoft
UPDATE     3           jun wen li           jun wen li           HP                   alibaba
UPDATE     4           jin hao liu          jin hao liu          IBM                  siemens
INSERT     5           NULL                 cheng fan            NULL                 Bank

(4 row(s) affected)