Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

Python

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


SQL Using Insert Exec

T-SQL Insert Exec

This tutorial covers how to use Insert Exec to extract data returned from a stored procedure into a table for manipulation. It also covers some of the issues associated with using Insert Exec within T-SQL code.

Getting Started Using T-SQL Insert Exec

To start, I have a Stored Procedure that returns data.

EXEC Sales.usp_SalesPerformance

Stored Proc Data

I would like to be able to get this data, import it into a temp table and modify it. Using an INSERT EXEC to a temp table will get the job done.

Step 1: Create the Table

Write code to create the table with the columns that are returned from the stored procedure. Check the data typees that are returned to make sure there will not be any errors generated at the time the INSERT EXEC is performed.

CREATE TABLE #tmp (
    SalesPersonID [int] NULL,
    SalesName [nvarchar](255) NULL,
    Address1 [nvarchar](150) NULL,
    Address2 [nvarchar](150) NULL,
    Address3 [nvarchar](255) NULL,
    CountryRegionName [nvarchar](50) NULL,
    SalesYTD money NULL,
    PerformanceRating [nvarchar](100) NULL
)

Step 2: Write the INSERT EXEC statement

Create the line of code to import the data into the temporary table.

INSERT INTO #tmp
    EXEC Sales.usp_SalesPerformance

Step 3: Create SQL Code to Test

Write the code to modify the data and do some work.

ALTER TABLE #tmp
    ADD IntlAddr bit NOT NULL DEFAULT (0)

UPDATE #tmp
    SET IntlAddr = 1
    WHERE CountryRegionName is Null or
    CountryRegionName not like 'United States'

    SELECT * FROM #tmp

    DROP TABLE #tmp

The modified data is returned.

Insert Exec Result

SQL INSERT EXEC Issues and Notes

While INSERT EXEC is a great way to quickly import data into a temp table, there are a few problems with it.

** INSERT EXEC cannot be "nested"**

If the stored procedure that was called here, Sales.usp_SalesPerformace, has an INSERT EXEC inside of it, the following error will occur.

Msg 8164, Level 16, State 1, Procedure usp_SalesPerformance, Line 35
An INSERT EXEC statement cannot be nested.

**INSERT EXEC cannot be used inside of a Function**

If the #tmp table created above were an existing table in the database (temp tables cannot be accessed within a function - a different problem), an error would be generated while trying to create\alter it.

Msg 443, Level 16, State 14, Procedure ufnGetStock, Line 13
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

**INSERT EXEC must match the table exactly**

It requires that the table receiving the data from the stored procedure match the columns exactly. If they do not, an error is generated.

Msg 213, Level 16, State 7, Procedure usp_SalesPerformance, Line 21
Column name or number of supplied values does not match table definition.