Microsoft SQL Server 2012 Bible

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

Each of these INSERT forms is useful for a unique task, often depending on the source of the data being inserted.

SQL Server complements the SQL INSERT commands with other tools to aid in moving large amounts of data or performing complex data conversions. The venerable Bulk Copy Wizard and the Copy Database Wizard are introduced in Chapter 23, “Transferring Databases.” The Copy Database Wizard actually creates a simple Integration Services package. Chapter 52, “Building, Deploying, and Managing ETL Workflows in Integration ...