Sample of Update~Delete with OUTPUT clause (2016 >), Sql Server - rextester

Run Code | API | Code Wall | Misc | Feedback | Login | Theme | Privacy | Patreon

Sample of Update~Delete with OUTPUT clause (2016 >)

Language: Layout:

/*
Microsoft TSQL Crash Course
http://www.forta.com/books/0672337924/
Setup:
*/
CREATE TABLE customers
( cust_id      INT        NOT NULL IDENTITY(1,1)
, cust_name    NCHAR(50)  NOT NULL 
, cust_address NCHAR(50)  NULL 
, cust_city    NCHAR(50)  NULL 
, cust_state   NCHAR(5)   NULL 
, cust_zip     NCHAR(10)  NULL 
, cust_country NCHAR(50)  NULL 
, cust_contact NCHAR(50)  NULL 
, cust_email   NCHAR(255) NULL
);
SET IDENTITY_INSERT customers ON;
INSERT INTO			customers
 (cust_id	, cust_name			, cust_address			, cust_city	, cust_state, cust_zip	, cust_country	, cust_contact	,	cust_email)
VALUES
 (10001		, 'Coyote Inc.'		, '200 Maple Lane'		, 'Detroit'	, 'MI'		, '44444'	, 'USA'			, 'Y Lee'		,	'ylee@coyote.com'		)
,(10002		, 'Mouse House'		, '333 Fromage Lane'	, 'Columbus', 'OH'		, '43333'	, 'USA'			, 'Jerry Mouse'	,	null					)
,(10003		, 'Wascals'			, '1 Sunny Place'		, 'Muncie'	, 'IN'		, '42222'	, 'USA'			, 'Jim Jones'	,	'rabbit@wascally.com'	)
,(10004		, 'Yosemite Place'	, '829 Riverside Drive'	, 'Phoenix'	, 'AZ'		, '88888'	, 'USA'			, 'Y Sam'		,	'sam@yosemite.com'		)
,(10005		, 'E Fudd'			, '4545 53rd Street'	, 'Chicago'	, 'IL'		, '54545'	, 'USA'			, 'E Fudd'		,	null					)
;
SET IDENTITY_INSERT customers OFF;

SELECT '-- CUSTOMERS table loaded...' as "-- comment1";
SELECT '-- Sample UPDATE with OUTPUT...' as "-- comment2";

UPDATE 
		customers
SET		cust_state = Upper(cust_state)
OUTPUT	DELETED.*
;

View schema
Execution time: 0,02 sec, rows selected: 1, rows affected: 10, absolute service time: 0,17 sec

edit mode | history | discussion

  
-- comment

1
-- CUSTOMERS table loaded...


  
cust_id
cust_name
cust_address
cust_city
cust_state
cust_zip
cust_country
cust_contact
cust_email

1
10001
Coyote Inc.                                       
200 Maple Lane                                    
Detroit                                           
MI   
44444     
USA                                               
Y Lee                                             
[email protected]                                                                                                                                                                                                                                                

2
10002
Mouse House                                       
333 Fromage Lane                                  
Columbus                                          
OH   
43333     
USA                                               
Jerry Mouse                                       
NULL

3
10003
Wascals                                           
1 Sunny Place                                     
Muncie                                            
IN   
42222     
USA                                               
Jim Jones                                         
[email protected]                                                                                                                                                                                                                                            

4
10004
Yosemite Place                                    
829 Riverside Drive                               
Phoenix                                           
AZ   
88888     
USA                                               
Y Sam                                             
[email protected]                                                                                                                                                                                                                                               

5
10005
E Fudd                                            
4545 53rd Street                                  
Chicago                                           
IL   
54545     
USA                                               
E Fudd                                            
NULL

λ

                                      .NET NoSQL database for rapid development