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