laika222
12/5/2018 - 8:56 PM
OUTPUT Data
/*********************************
OUTPUT DATA
*********************************/
/*
SQL Server supports an OUTPUT clause for modification statements that lets you return information from
the modified rows. You can use this output for purposes like auditing, archiving, etc.
The design of the OUPUT clause is very similar to that of the SELECT clause in the sense that you can specify
expressions and assign them with result column aliases. One difference however is that in the OUPUT clause,
when you refer to columns from the modified rows, you need to prefix the column names with the keywords
*deleted* or *inserted*. Use the prefix inserted when the rows are inserted rows and the prefix deleted when
they are deleted rows. In an UPDATE statement, inserted represents the state of the rows after the update
and deleted represents the state before the update. Note that *inserted* can reference a column that was
not updated in the merge as long as the row was updated. For example, if you update the price column and don't
update the id column for a row, you can use inserted.price and inserted.id to get both of those
columns into the output, even though technically nothing was inserted/changed in the column id for that row.
You can have the OUTPUT clause return a result set back to the caller much like a SELECT does. Alternately,
you can add an INTO clause to direct the output rows into a target table. You can use multiple OUTPUT
statements, one with an INTO to insert into a table, and one without an INTO in order to the result to the
user. If you use an INTO clause, the target table cannot participate in either side of a foreigh key relationship
and can not have triggers defined on it.
*/
/*********************************
OUTPUT WITH AN INSERT
*********************************/
/*
For example, say you have the following table products1:
id product price
----------- -------------------- ---------------------
1 guitar 2.50
2 keyboard 882.00
3 shaker 22.75
4 oboe 8863.33
And in the products2 table you have:
id product price
----------- -------------------- ---------------------
11 drum machine 18.11
2 trombone 325.00
8 claves 1.20
10 drums 98.99
15 uke 13.32
*/
INSERT INTO products1 (id, product, price)
OUTPUT inserted.id, inserted.product, inserted.price -- the OUPUT statement, using the 'inserted' keyword before the column names
SELECT id, product, price -- start the SELECT statement which tells which rows to INSERT into products1
FROM products2
WHERE product = 'drum machine'
/*
The OUPUT clause will return what was INSERTed into the products1 table.
id product price
----------- -------------------- ---------------------
11 drum machine 18.11
As well, if you SELECT * FROM products1 you'll see that the INSERT clause will add the row to products1.
id product price
----------- -------------------- ---------------------
1 guitar 2.50
2 keyboard 882.00
3 shaker 22.75
4 oboe 8863.33
11 drum machine 18.11
You can also use an OUTPUT...INTO statement to take the OUTPUT results and INSERT into some other table.
In this example, the INSERT INTO takes rows from products1 and INSERTs INTO products2. The OUPUT #1
clause takes the OUTPUT results and INSERTs into a third table called destination. The OUPUT #2 statement
takes that same OUTPUT result set and returns it to the user.
*/
INSERT INTO products1 (id, product, price)
OUTPUT inserted.id, inserted.product, inserted.price -- OUPUT #1 with INTO
INTO destination (id, product, price) -- take OUTPUT #1 result and put INTO table destination
OUTPUT inserted.id, inserted.product, inserted.price -- OUTPUT #2 statement to return OUPUT results to user
SELECT id, product, price -- start SELECT statement to pick which rows to INSERT into products1
FROM products2
WHERE product = 'drum machine'
/*
The OUTPUT #2 clause will return the OUTPUT of the INSERTed rows.
id product price
----------- -------------------- ---------------------
11 drum machine 18.11
As well, if you SELECT * FROM destination you'll see that the OUTPUT has also been added to that table.
id product price
----------- -------------------- ---------------------
11 drum machine 18.11
Finally, if you SELECT * FROM products1, you'll see that the original INSERT INTO statement has indeed
INSERTed the row into products1.
id product price
----------- -------------------- ---------------------
1 guitar 2.50
2 keyboard 882.00
3 shaker 22.75
4 oboe 8863.33
11 drum machine 18.11
*/
/*********************************
OUTPUT WITH A DELETE
*********************************/
/*
You can use OUPUT to return information returned in frows from a DELETE statment. You have to
prefix the row names with the deleted keyword.
*/
DELETE FROM products2 -- delete from products table
OUTPUT deleted.id, deleted.product, deleted.price -- OUTPUT statement using the deleted keyword prior to the column names
WHERE id > 9 -- WHERE clause for DELETE statement
/*
The result from the OUTPUT statement showing the information from the DELETEd rows.
id product price
----------- -------------------- ---------------------
10 drums 98.99
15 uke 13.32
*/
/*********************************
OUTPUT WITH AN UPDATE
*********************************/
/*
You can use OUTPUT with an UPDATE stement. You have access to both the information prior to the update
and information after the UPDATE. To refer to information prior to the update, use the deleted keyword,
and to access information after the update, use the inserted keyword.
For example, say the state of products1 is:
id product price
----------- -------------------- ---------------------
1 guitar 2.50
2 keyboard 882.00
3 shaker 22.75
4 oboe 8863.33
*/
UPDATE products1
SET price = price * 1.10
OUTPUT
inserted.id -- the id after the change
, inserted.product -- the product after the change
, deleted.price AS 'Old Price' -- the price before the change
, inserted.price AS 'New Price' -- the price after the change
/*
Result from the OUTPUT statement
id product Old Price New Price
----------- -------------------- --------------------- ---------------------
1 guitar 2.50 2.75
2 keyboard 882.00 970.20
3 shaker 22.75 25.025
4 oboe 8863.33 9749.663
*/
/*********************************
OUTPUT WITH A MERGE
*********************************/
/*
You can use OUTPUT with a MERGE statement. A MERGE can apply different actions against the target table,
so SQL Server includes the $action function which allows you to see which action was taken: INSERT, UPDATE,
or DELETE.
For example, say you have two tables products1 and products2 in this state:
products1:
id product price
----------- -------------------- ---------------------
1 guitar 2.50
2 keyboard 882.00
3 shaker 22.75
4 oboe 8863.33
products2:
id product price
----------- -------------------- ---------------------
1 drum machine 18.11
2 trombone 325.00
8 claves 1.20
10 drums 98.99
15 uke 13.32
Say you complete a MERGE updating products1 with products2 as a source, and this MERGE will UPDATE,
INSERT, and DELETE rows. You can use an OUTPUT statement to return the results of the MERGE, using
the $action function to tell you which action was completed.
*/
/*
The result of the OUTPUT statement:
Action id product Old Price New Price
---------- ----------- -------------------- --------------------- ---------------------
INSERT 8 claves NULL 1.20
INSERT 10 drums NULL 98.99
INSERT 15 uke NULL 13.32
UPDATE 1 drum machine 2.50 18.11
UPDATE 2 trombone 882.00 325.00
DELETE 3 shaker 22.75 NULL
DELETE 4 oboe 8863.33 NULL
The result of SELECT * from products1 after the MERGE:
id product price
----------- -------------------- ---------------------
1 drum machine 18.11
2 trombone 325.00
8 claves 1.20
10 drums 98.99
15 uke 13.32
*/
/*
Example of a merge with an output
*/
CREATE TABLE LT_M2_UNSUBSCRIBE_IMPORT_CONTACT_PERMISSIONS_20190605 (
action VARCHAR(50)
, id INT
, answer_before VARCHAR(1)
, answer_after VARCHAR(1)
, customer_no INT
, last_asked_dt DATETIME
, type_id INT
, create_dt DATETIME
, create_loc VARCHAR(50)
, created_by VARCHAR(50)
, last_update_dt DATETIME
, last_updated_by VARCHAR(50)
)
-- CTE TO GET customer_nos FOR RECORDS THAT HAVE EMAIL ADDRESSES ON MAIL2 UNSUBSCRIBE LIST
;WITH CTE_CUST_NOS_WITH_EMAIL(customer_no)
AS
(
select distinct customer_no from T_EADDRESS where address IN(select RTRIM(email) from lt_m2_unsub_import_test)
)
-- START MERGE
MERGE INTO TX_CUST_CONTACT_PERM_TYPE
USING CTE_CUST_NOS_WITH_EMAIL
ON TX_CUST_CONTACT_PERM_TYPE.customer_no = CTE_CUST_NOS_WITH_EMAIL.customer_no
AND TX_CUST_CONTACT_PERM_TYPE.type_id = 2 -- Development Email
-- IF CONTACT PERMISSION ALREADY EXISTS FOR DEVELOPMENT EMAIL, SET ANSWER TO 'N'
WHEN MATCHED THEN UPDATE
SET TX_CUST_CONTACT_PERM_TYPE.answer = 'N'
-- IF CONTACT PERMISSION DOESN'T ALREADY EXIST FOR DEVELOPMENT EMAIL, INSERT ROW WITH ANSWER 'N'
WHEN NOT MATCHED THEN INSERT
(
answer
, customer_no
, last_asked_dt
, type_id
, create_dt
, create_loc
, created_by
, last_update_dt
, last_updated_by
)
VALUES (
'N'
, CTE_CUST_NOS_WITH_EMAIL.customer_no
, GETDATE()
, 2 -- Development Email
, GETDATE()
, 'FIN-EA5WIN10'
, 'dbo'
, GETDATE()
, 'dbo'
)
-- OUTPUT BEFORE AND AFTER RESULTS TO HISTORY TABLE LT_M2_UNSUBSCRIBE_IMPORT_CONTACT_PERMISSIONS_20190605
OUTPUT
$action
, inserted.id
, inserted.customer_no
, deleted.answer
, inserted.answer
, inserted.type_id
INTO LT_M2_UNSUBSCRIBE_IMPORT_CONTACT_PERMISSIONS_20190605 (
action
, id
, customer_no
, answer_before
, answer_after
, type_id)
;
/*********************************
NESTING DATA MANIPULATION LANGUAGE SO YOU CAN USE WHERE CLAUSE WITH OUTPUT STATEMENT
*********************************/
/*
An OUTPUT statement doesn't allow you to filter the rows returned, but sometimes you only want to look
at certain rows. For example, say you're UPDATING a bunch of rows and you want to insert into a second
table those UPDATEd rows where the price is less than $100 (and ignore those rows where the price is
$100 or above). You can do this by nesting the statement with the OUTPUT clause in a derived table (nested DML),
and then from that derived table use an INSERT INTO statement that uses a WHERE clause, filtering out the
rows that you don't want.
*/
INSERT INTO destination (id, product, price)
SELECT id, product, newprice
FROM ( -- begin the nested DML statement
UPDATE products1
SET price = price * 1.10
OUTPUT -- OUTPUT which will output all rows updated
inserted.id
, inserted.product
, CAST(inserted.price AS MONEY) AS newprice) AS a
WHERE a.newprice <= 100 -- outer select which rows with a price of less than $100, which filters what you're INSERTing INTO destination
/* Result of OUTPUT filtering using an outer WHERE clause so you can only insert those rows into
the table called destination.
id product price
----------- --------------------- ---------------------
1 drum machine 21.9131
8 claves 1.452
15 uke 16.1172
*/