293
Chapter 12: Modifying Data In SQL Server
12
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 Workfl ows in Integration Services,” details Integration Services, a powerful tool that can
move and manipulate large sets of data between/among nearly any data sources.When inserting new data, if the table has surrogate keys, then primary key values must be
generated to identify the new rows. Although identity columns and GUIDs both make excel-
lent primary keys, each requires special handling during the insertion of rows. This section
describes how to create identity-column values and GUIDs.Inserting Simple Rows of Values
The simplest and most direct method to insert data is the INSERT...VALUES method.
Until SQL Server 2008, INSERT...VALUES was limited to inserting a single row, but SQL
Server is now compliant with the ANSI standard and can include row constructors —
inserting multiple rows in a single INSERT...VALUES statement:INSERT [INTO] schema.table [(columns, ...)]
VALUES (value,...), (value,..., ... ;Building an INSERT...VALUES statement is mostly straightforward, although you do have
a few options. The INTO keyword is optional and is commonly ignored. The key to building
an INSERT statement is getting the columns correctly listed and ensuring that the data
type of the value is valid for the inserted column.When the values are inserted into a new row, each value corresponds to an insert column.
The insert columns may be in any order — the order of the columns within the table is
irrelevant — as long as the insert columns and the value columns in the SQL INSERT
command are in the same order.As with every chapter that includes code, the fi le Ch 12 - Modifying Data.sql on www
.SQLServerBible.com contains all the sample code for this chapter.Before any data can be inserted, fi rst start by running the following script:Use AdventureWorks
GO
CREATE TABLE dbo.[Address]
(
AddressID int identity(1,1)c12.indd 293c12.indd 293 7/30/2012 4:42:38 PM7/30/2012 4:42:38 PM
http://www.it-ebooks.info