Microsoft® SQL Server® 2012 Bible

299

Chapter 12: Modifying Data In SQL Server

12

INSERT INTO Address

Exec ListAZAddresses;

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

WHERE

State = 'AZ';

Result (abbreviated):

AddressLine1 City StateProvinceCode County PostalCode

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

137 Lancelot Dr Phoenix AZ Sunshine 85004

25250 N 90th St Scottsdale AZ Sunshine 85257

253731 West Bell Road Surprise AZ Sunshine 85374

2551 East Warner Road Gilbert AZ Sunshine 85233

3294 Buena Vista Lemon Grove AZ Sunshine 85284

4584 Hamiliton Ave. Chandler AZ Sunshine 85225

6441 Co Road Lemon Grove AZ Sunshine 85252

6500 East Grant Road Tucson AZ Sunshine 85701

7656 Ramsey Circle Chandler AZ Sunshine 85225

7709 West Virginia Avenue Phoenix AZ Sunshine 85004

7750 E Marching Rd Scottsdale AZ Sunshine 85257

870 N. 54th Ave. Chandler AZ Sunshine 85225

9228 Via Del Sol Phoenix AZ Sunshine 85004

9980 S Alma School Road Chandler AZ Sunshine 85225

...

INSERT/EXEC does require more work than INSERT...VALUES or INSERT/SELECT, but

because the stored procedure can contain complex logic, it’s the most powerful of the three.

In addition to inserting the results of a stored procedure execution into a physical table,

you can also insert into variable tables, which can be seen in the following script:

declare @address TABLE

(

Address1 varchar(25) NOT NULL,

City varchar(13) NOT NULL,

StateAbbrev char(3) NOT NULL,

County varchar(10) NULL,

PostalCode varchar(10)

)

insert into @address

exec ListAZAddresses

select * from @address

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

http://www.it-ebooks.info