Microsoft® SQL Server® 2012 Bible

298

Part II: Building Databases and Working with Data

Burbank CA 1008 Lydia Lane Sunshine 91502

Berkeley CA 1011 Yolanda Circle Sunshine 94704

Burbank CA 1016 Park Avenue Sunshine 91502

The key to using the INSERT/SELECT statement is selecting the correct result set. It’s a

good idea to run the SELECT statement by itself to test the result set prior to executing

the insert. Measure twice, cut once.

Inserting the Result Set from a Stored Procedure

The INSERT...EXEC form of the INSERT operation pulls data from a stored procedure and

inserts it into a table. Behind these inserts are the full capabilities of T-SQL. The basic

function is the same as that of the other insert forms. The columns must line up between

the INSERT columns and the stored-procedure result set. Following is the basic syntax of

the INSERT...EXEC command:

INSERT [INTO] schema.Table [(Columns)]

EXEC StoredProcedure Parameters;

Be careful, though, because stored procedures can easily return multiple record sets, in

which case the INSERT attempts to pull data from each of the result sets, and the columns

from every result set must line up with the insert columns.

For more about programming stored procedures, refer to Chapter 17, “Developing Stored Procedures.”

The following code sample builds a stored procedure that returns 10 Arizona addresses from

the Address table in the AdventureWorks database. When the stored procedure is in place,

the sample code performs the INSERT...EXEC statement:

use tempdb

go

IF(OBJECT_ID('ListAZAddresses')) IS NOT NULL

DROP PROC ListAZAddresses

GO

CREATE PROC ListAZAddresses

AS

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 = 'Arizona';

To insert the results set of the stored procedure execution into the Address table use the

following:

USE AdventureWorks

Go

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

http://www.it-ebooks.info