Chapter 10: DML

Question 1

Why is it recommended to specify the target column names in INSERT statements?

Answer 1

Because then you don’t care about the order in which the columns are defined in the table. Also, you won’t be affected if the column order is rearranged due
to future definition changes, in addition to when columns that get their values automatically are added.

Question 2

What is the difference between SELECT INTO and INSERT SELECT?

Answer 2

SELECT INTO creates the target table and inserts into it the result of the query. INSERT SELECT inserts the result of the query into an already existing table.

Question 3

It’s possible insert data with a stored procedure using INSERT statement?

Answer 3

Yes, it is.

Question 4

What does do the OUTPUT clause in a INSERT INTO statement?

Answer 4

All the data which it’s going to be inserted in the target table, it’s also inserted in another table for auditing purposes. For use the OUTPUT clause, the syntax is: OUTPUT Inserted.col1, Inserted.col2, …, Inserted.coln INTO @AuditTable

Question 5

How I can add a row ignoring the identity property column?

Answer 5

Using the SET IDENTITY_INSERT table ON. After the insert statement it’s issued, it needs to be OFF.

Question 6

What’s the standard syntax for an UPDATE statement?

Answer 6

UPDATE 
SET
       = 
       = 
                           ...
       = 
[WHERE ]

Question 7

Could an UPDATE statement use a FROM clause and joins?

Answer 7

Yes, it could. The syntax would look like this:

UPDATE
SET

FROM
WHERE

Question 8

How I can store the values changed during an UPDATE statement?

Answer 8

You could use a variable and store the value updated. It would be like this:

DECLARE @variable AS ;

UPDATE
SET @variable = =

Once the prior statement is executed, @variable will containt the value updated.

Question 9

In an UPDATE statement, does all SET references executes at the same time?

Answer 9

Yes, it does. Let’s suppose we have the following UPDATE statement:

UPDATE table
SET 
    col1 = 'a',
    col2 = col1
WHERE keycol = 1

You’ll think that col2 will have the value which contains the col1 (which is ‘a’ once is updated), but actually it’ll containt the prior value of col1 before is updated. This is called all-at-once.

Question 10

What’s the syntax for achieve a DELETE statement?

Answer 10

DELETE [FROM]
[WHERE ]

If there’s not a WHERE clause specified, then it’ll delete all rows from the table.

Question 11

What does do the MERGE statement?

Answer 11

As his clause’s name indicates: it merges (join together) a source table into a target table, and it allows the three DML statements together (INSERT, DELETE, UPDATE) according to matching predicates and simple predicates.

Question 12

What’s the syntax of the MERGE statement?

Answer 12

MERGE INTO
USING
ON
WHEN MATCHED [AND ] THEN {UPDATE | DELETE}

WHEN NOT MATCHED [BY TARGET] [AND ]
THEN {INSERT}

WHEN NOT MATCHED BY SOURCE [AND ]
THEN {UPDATE | DELETE}

Question 13

Is the USING clause from the MERGE statement like FROM clause in the SELECT statement?

Answer 13

Yes, it is.

Question 14

Is the USING clause from the UPDATE/DELETE statement like FROM clause in the SELECT statement?

Answer 14

Yes, it is.

Question 15

What’s used for the OUTPUT clause?

Answer 15

It shows/store the data according to an action, such as inserted, updated or deleted.

Question 16

How I can use the OUTPUT clause along with INSERT, DELETE and UPDATE statement?

Answer 16

INSERT:

INSERT INTO
OUTPUT inserted.*
[INTO ]

the inserted prefix needs to be present for specify to SQL Server that there’s a bunch of values inserted.

DELETE:

DELETE [FROM]
OUTPUT deleted.*
[INTO ]

the deleted prefix needs to be present for specify to SQL Server that there’s a bunch of values deleted.

UPDATE:

UPDATE 
SET 
OUTPUT
             {deleted. | inserted.*}
        [INTO ]

there’s not a specific prefix for UPDATE statement. The deleted prefix refers to value before being updated, and hte inserted prefix refers to value after being updated.

Question 17

Is it possible use the OUTPUT clase along with MERGE statement?

Answer 17

Yes, it is.

MERGE [INTO]
USING
ON
WHEN MATCHED [AND ] THEN {UPDATE | DELETE}

WHEN NOT MATCHED [BY TARGET] [AND ]
THEN {INSERT}

WHEN NOT MATCHED BY SOURCE [AND ]
THEN {UPDATE | DELETE}
OUTPUT
       [$action],
       [inserted.* | deleted.*]

the $action is a function that tells the action performed in the MERGE statement, such as UPDATE, INSERT or DELETE.