How to Insert Stored Procedure Results Into Table/Temp Table

Stored procedures are the most efficient way to call and retrieve the data. It's easy and efficient to encapsulate the logic in them and call them when needed. With this use, sometimes you need to store the data into a table to use it.

There may be the two scenarios:

1. Table Created Beforehand

If we know the schema of the result set of the Stored Procedure, we can create a table beforehand and store the result set of Stored Procedure into that table.

--CREATING STORED PROCEDURE 

CREATE PROCEDURE EmpDetail

      @Emp_Name VARCHAR(20),

      @Emp_Id VARCHAR(20)

AS

BEGIN

      CREATE TABLE #T_EMPDETAIL(EMP_NAME VARCHAR(20),EMP_ID VARCHAR(10))

      INSERT INTO #T_EMPDETAIL VALUES(@Emp_Name,@Emp_Id)

      SELECT * FROM #T_EMPDETAIL

      DROP TABLE #T_EMPDETAIL

END

--CREATE A TABLE TO STORE THE RESULT SET OF STORED PROCEDURE

CREATE TABLE #T_emp(EMP_NAME VARCHAR(20),EMP_ID VARCHAR(10))

--TO INSERT STORED PROCEDURE RESULT INTO TABLE

INSERT INTO #T_emp EXEC EmpDetail 'BRAJESH','BRAJESH456'

--NOW LET'S SEE WHAT IS IN TABLE

SELECT * FROM #T_emp

OUTPUT

EMP_NAME	EMP_ID
BRAJESH	BRAJESH456

  

The disadvantage of this code is if  Stored Procedure returns more or less columns, due to any reason, It will throw an error.

2. Table Created at Runtime