Learn How to Insert Data From Stored Procedure Into Table?

Stored Procedures are considered most efficient way for calling and retrieving data and hence users always look forward to encapsulate logic in it so that it can be called when needed. While encapsulating data in Stored Procedure, it is also important to know how to retrieve data they return. Now, the question is how to insert data from stored procedure into table? It can be a simple task when the datatypes and columns are known, but when these datatypes are not known, it can get difficult. This blog discusses about two methods using which users can know how to insert data from Stored Procedure into Table. One method will describe a method where table is already available and one is where table is created in run time. This blog will highlight both of these procedures and users can utilize the one, which is required.

Step 1: Create A Stored Procedure

The first step of this discussion will be creation of Stored Procedure. Below mentioned script can be used for this.

Create procedure

Below mentioned script can be used to execute this stored procedure;

Get Database Name

Step 2: Insert Data from Stored Procedure into Table

Scenario 1: When schema is known and table is already created.

When the schema of Stored Procedure resultset is known the table can be created and executed using following code.

Insert Data From Stored Procedure

Caveats: This process can be an easy task but the only issue with this operation is if the Stored Procedure returns less or more columns than defined, it will show an error.

Scenario 2: When schema is not known and Table is created at runtime.

In this case, the table has to be populated according to the stored Procedure in Run time. In such case, resultset of the Stored Procedure is not known. Following code can be executed for such scenario;

Select Into

Caveat: This method can get more difficult and works well when users are not aware of column names. In case this method is throwing errors, enable ad hoc distributed queries. This can be done through execution of below mentioned query;

Show Advance Option


In this way, the data from stored procedure can be inserted to table in SQL Server. These methods can be implemented for different scenarios and the data from Stored Procedure can be inserted to Table by creating Table prior to execution or while run-time.

About Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management etc.
This entry was posted in SQL and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s