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
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.
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.
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.
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.
Column name or number of supplied values does not match table definition.