Microsoft® SQL Server® 2012 Bible

297

Chapter 12: Modifying Data In SQL Server

12

FROM data sources

[WHERE conditions];

As with the INSERT...VALUES statement, the data columns must line up, and the data

types must be valid. If the optional insert columns are ignored, then every table column

(except an identity column) must be populated in the table order.

The following code sample uses the AdventureWorks database. It selects the fi rst 10

California addresses from the Address table and inserts them into the Address table

created in tempdb. All the columns are pulled directly from the table, while the county is a

string literal (the Address table is specifi ed by means of a three-part name, database

.schema.table):

use AdventureWorks

go

INSERT INTO Address

SELECT TOP(10)

AddressLine1, City, sp.StateProvinceCode, 'Sunshine', PostalCode

FROM Person.Address a

INNER JOIN Person.StateProvince sp

ON a.StateProvinceID = sp.StateProvinceID

WHERE

sp.Name = 'California';

To verify the insert, the following SELECT statement reads the data from the Address

table:

Use AdventureWorks

go

SELECT AddressID, City, State,

Address1, County, PostalCode

FROM dbo.Address;

Result:

City State address1 County PostalCode

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

Houston TX 1411 Mesa Road NULL 77016

Baton Rouge LA 444 Perkins Road East Baton Rouge 70808

Chicago IL 8765 Buttonwood Walk Cook 60429

Seattle WA 3333 Pike Street Pike 23674

Memphis TN 99934 Orange Ct Vols 74944

Los Angeles CA 1 Smiling Tree Court Sunshine 90012

Berkeley CA 1002 N. Spoonwood Court Sunshine 94704

Colma CA 1005 Fremont Street Sunshine 94014

Mill Valley CA 1005 Matterhorn Ct. Sunshine 94941

Bellflower CA 1006 Deercreek Ln Sunshine 90706

Torrance CA 1006 Deercreek Ln Sunshine 90505

El Cajon CA 1007 Cardinet Dr. Sunshine 92020

c12.indd 297c12.indd 297 7/30/2012 4:42:39 PM7/30/2012 4:42:39 PM

http://www.it-ebooks.info