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.

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

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.

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;

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;

Conclusion

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.