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.

referenceaero 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 ...

Get Microsoft SQL Server 2012 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.