Microsoft® SQL Server® 2012 Bible

296

Part II: Building Databases and Working with Data

So far in the sample code, values have been hard-coded string literals. Alternatively, the

value could be returned from an expression. This is useful when a data type requires con-

version, or when data needs to be altered, calculated, or concatenated:

Use AdventureWorks

Go

INSERT INTO dbo.Address

VALUES('99934'+' Orange Ct', 'Memphis', 'TN','Vols', '74944');

The next SELECT statement verifi es the insert:

Use AdventureWorks

go

SELECT AddressID, City, State, Address1, County, PostalCode

FROM dbo.Address;

Result:

AddressID City State address1 County PostalCode

--------- ------------ ----- --------------------- ----------------- ----------

1 Houston TX 1411 Mesa Road NULL 77016

2 Baton Rouge LA 444 Perkins Road East Baton Rouge 70808

3 Chicago IL 8765 Buttonwood Walk Cook 60429

5 Seattle WA 3333 Pike Street Pike 23674

6 Memphis TN 99934 Orange Ct Vols 74944

When the data to be inserted, usually in the form of variables sent from the user interface,

is known, inserting using the INSERT...VALUES form is the best insert method.

Typically, to reference values from a data source, the INSERT...SELECT is used, but an

INSERT...VALUES can include a scalar subquery as one of the values.

The topic of subqueries is discussed in detail in Chapter 9, Merging Data with Joins, Subqueries,

and CTEs.

Inserting a Result Set from Select

You can move and massage data from one result set into a table by means of the

INSERT...SELECT statement. The real power of this method is that the SELECT command

can pull data from nearly anywhere and reshape it to fi t the current needs. It’s this fl exibil-

ity that the INSERT...SELECT statement exploits. Because SELECT can return an infi nite

number of rows, this form can insert an infi nite number of rows.

Of course, the full power of the SELECT can generate rows for the insert. A simplifi ed form

of the syntax follows:

INSERT [INTO] schema.Table [(columns, ...)]

SELECT columns

c12.indd 296c12.indd 296 7/30/2012 4:42:38 PM7/30/2012 4:42:38 PM

http://www.it-ebooks.info