The Influence of DML statement acquisition in the introduction of T-SQL

First, finish the unfinished lecture in the previous section:

Multi-column update

UPDATE testbook

SET price = price * 0.3, -- discount

Remark = 'outdated Book' -- Remarks

WHERE date <= '2014-01-01 ';

Large-capacity data update

GO

UPDATE file_table

SET FileName='Img100.jpg ',

DOCUMENT = (

SELECT *

From openrowset (BULK 'C:/Img100.jpg ', SINGLE_BLOB) AS Document

)

WHERE filetypepolic'.jpg ';

Delete data

(1) Delete some data

Delete from book2 -- DELETE some data

Where date <'2014/1/1 ';

(2) Delete all data

Delete from book2 -- DELETE all data

You can also delete all data in this way.

Truncate table book2

Note: in terms of the effect, the truncate table and the DELETE table are the same. All data in the table is deleted, but the table structure is not included. But they are different:

(1) The system executes the delete statement to delete the data row by row and record the log information.

(2) When the truncate statement is executed, the system will delete all data pages related to the table at one time, without logging or forming a log file.

Based on the above highlights, we can see that the truncate table is much faster than the delete table, because the former does not record logs. However, this also indicates that the data deleted by the truncate table statement cannot be restored using the rollback statement. On the contrary, delete is acceptable.

View data tables

SELECT a. name field name,

B. name field type,

A. Length of the max_length Field

FROM sys. all_columns as

JOIN sys. types as B

ON a. system_type_id = B. system_type_id

WHERE object_id = object_id ('dbo. student ');

Delete A data table

Drop table T1, T2, T3

2005 and later versions can obtain the specific impact information by using output in the operating statement.

Application of Output in Insert statements

INSERT book

Output inserted .*

SELECT 106, 'Digital image process', ' ', 'science press', '2014/1/1', '2014/1 ', 2006/7, 200607100, 'poorer'

Of course, you can also save the returned results, but you must use tables or table variables to accept the expected results.

IF OBJECT_ID ('t2', 'U') is not null drop table T2; -- create a TABLE structure

GO

Create table T2

(

Bkno int,

Bkname nvarchar (50 ),

Author nvarchar (8)

);

INSERT book

Output inserted. bkno, INSERTED. bkname, INSERTED. author

INTO T2

SELECT 104, 'jsp website development example', 'zhao min', 'electronics Industry Publishing House ', '2017/11', '20170301', 2002/10, 'good'

UNION ALL

SELECT 105, 'c ++ Builder programming designer ', 'yu Linzhou', 'people's post and telecommunications publishing Publishing House ', '2014/1', '123', 2007/1, 'average'

Application of Output in delete statement

Delete from book

Output deleted .*

WHERE bkno = 104;

Application of Output in update statement

DECLARE @ MyTableVar TABLE -- create a TABLE variable to act like a TABLE

(

Preprice numeric (10, 2 ),

Posprice numeric (10, 2)

)

UPDATE book

SET price = price * 1.3

Output deleted. price,

INSERTED. price

INTO @ MyTableVar;