Microsoft® SQL Server® 2012 Bible

292

Part II: Building Databases and Working with Data

Best Practice

The SQL INSERT, UPDATE, DELETE, and MERGE commands are actually verb extensions of the basic

SELECT command. The full potential of the SELECT command lies within each data-modifi cation opera-

tion. Even when modifying data, you should think in terms of sets, rather than single rows.

Data-modifi cation commands may be submitted to SQL Server from any one of several inter-

faces. This chapter is concerned more with the strategy and use of the INSERT, UPDATE,

DELETE, and MERGE commands than with the interface used to submit a given command to

SQL Server.

SQL Server Management Studio offers two interfaces for submitting SQL commands: Query

Designer and Query Editor. If you like a visual UI, then Query Designer may work for a

while, but you should migrate to Query Editor to enjoy the richness of T-SQL. You prob-

ably can do all your development work exclusively in Query Editor. Even though the Query

Designer offers a very intuitive UI it may present unseen challenges that could result in

invalid or incorrect data.

For more details on using Management Studio’s Query Designer and Query Editor, see Chapter 5, “SQL

Server Management and Development Tools.”

Inserting Data

SQL offers six forms of INSERT and SELECT/INTO as the primary methods to insert data (as

shown in Table 12-1). The most basic method simply inserts a row of data, whereas the most

complex builds a data set from a complex SELECT statement and creates a table from the result.

TABLE 12-1 Insert Forms

Insert Form Description

INSERT/VALUES Inserts one or more rows of values; commonly used to insert data

from a user interface

INSERT/SELECT Inserts a result set; commonly used to manipulate sets of data

INSERT/EXEC Inserts the results of a stored procedure; used for complex data

manipulation

INSERT/DEFAULT VALUES Creates a new row with all defaults; used for pre-populating

pigeonhole data rows

SELECT/INTO Creates a new table from the result set of a SELECT statement

MERGE Combines inserting, updating, and deleting data in a single

statement

c12.indd 292c12.indd 292 7/30/2012 4:42:37 PM7/30/2012 4:42:37 PM

http://www.it-ebooks.info