Insert results of stored procedure into temp table [SOLVED]

Overview of SQL Store Procedure

SQL store procedure is group of one or more SQL DML statements store together in logical unit .SQL store procedures.  SQL procedure allows business logic to be stored in the database and executed from SQL statements.

 

Syntax to create store procedure in SQL

CREATE or ALTER PROCEDURE procedure_name(parameter1,parameter2,..)
	/*
	(
	@parameter1 int = 5,
	@parameter2 datatype OUTPUT
	)
	*/
AS
	/* SET NOCOUNT ON */
	RETURN

• Procedure_name : specify the user defined procedure name
• Parameter1, parameter2,.. : List of parameters to be passed with procedure. It may be zero or more based upon the user requirements
• SQL statements: specify any DML SQL statement insert, update, delete .

 

Syntax to execute or call SQL stored procedure

EXEC procedure_name  @parameter1=value1 @parameter2=value2

 

Examples of SQL store procedure

Consider student table of student result management system database to perform practical examples

Example: Create store procedure to retrieve student data from tblstudent based on city name passed as parameter

create PROCEDURE dbo.StoredProcedure2
@city varchar(10)
	
AS
select * from tblstudent where city=@city
RETURN

 

Overview on temporary table in SQL

SQL temporary table is used to store temporary data. We can perform select insert, update and delete operations on temp table like the permanent database tables. Temporary tables are store inside the Temporary Tables folder of the tempdb database. Temporary tables are dropped when the session that creates the table has closed, or can also be explicitly dropped by users.

 

Different methods to create a temp table in SQL

SQL temp table are created using SQL CREATE table statement followed by table name must be start with single ‘#’ sign.

There are two ways to create temp table in SQL

1. SQL Select into
2. SQL Create table

 

Method-1: Using SQL select into

Syntax of create temp table using SQL select into

SELECT column1, column2, column3,...  
INTO #name_of_temp_table
FROM table_name
WHERE condition

• In the above syntax SQL select statement is used to create temp table .
• Column1,column2,column3 .. are the name of columns to be added to temp table selected from table_name.
• #name_of_emp_table is the name of temporary table starts with ‘#’ sign.
• Table_name is the table name from which records are selected and inserted to temp table with column name.
• Where condition is optional, used to conditionally retrieve records from table_name.

 

Example: Write SQL query to create temp table #temp_student which contains studentid , studentname , email , city columns of student table.

SELECT  student_id, studentname, email, city
INTO    [#temp_student]
FROM   tblstudent
Select * from #temp_student

• Once we execute above query it will create #temp_student table with copy of select column value of all records.
• SQL select statement is used to retrieve records of #temp_student table.

OUTPUT:

 

Method-2: Using SQL create table statement

Syntax of create temp table using SQL create table statement

CREATE TABLE #name_of_temp_table (
	column_1 datatype,
	column_2 datatype,
	column_3 datatype,
..	column_n datatype
)

 

Example: Write SQL query to create temp table #temp_table_student which contains studentid, studentname, email , city columns of student table.

CREATE TABLE #temp_table_student (student_id int,
studentname varchar(20),
city varchar(20),
email varchar(25));
insert into #temp_table_student  values(101,'mahadev', 'Surat','maha@gmail.com')
select * from #temp_table_student

• In the above query SQL create table statement is used to create temp #temp_table_student.
• If we created temp table using SQL create table statement then we need to insert records using SQL insert into query.

OUTPUT:

 

Insert results of stored procedure into temp table

In the above sections we have discuss how to create store procedure, execute store procedure , how to create temp table and how to retrieve data from temp table.

Now we are going to discuss how to store result of store procedure into temp table

 

Syntax to insert result of stored procedure into temp table

CREATE or ALTER PROCEDURE procedure_name(parameter1,parameter2,..)
AS
@parameter1 datatype,
@parameter2 datatype
BEGIN;
Select column1,column2,…  into #temp_table from table_name [where condition];
Select * from #temp_table;
RETURN;

 

Example-1: Insert result of simple stored procedure into temp table

Write SQL store procedure to select all student and store into #temp_student_data temporary table

CREATE PROCEDURE dbo.StoredProcedurestudent
AS
select student_id,studentname,enrollmentno,city,email into #temp_student_data from tblstudent 
RETURN

Execute above created stored procedure

EXEC StoredProcedurestudent
select * from #temp_student_data

OUTPUT:

 

Example-2: Insert result of parameterized stored procedure into temp table

Example: Write SQL stored procedure to retrieve student records based on city name passed as parameter and stored into #temp_student_city table

CREATE PROCEDURE dbo.Spstudentcity
	@city varchar(10)
AS
select student_id,studentname,enrollmentno,city,email into #temp_student_city from tblstudent where city=@city
SELECT  student_id, studentname, enrollmentno, city, email
FROM   [#temp_student_city]
RETURN

Execute above created stored procedure

EXEC spstudentcity @city=surat
select student_id,studentname from #temp_student_city

OUTPUT:

 

Summary

In this article of Insert into temp table stored procedure, We have covered overview of SQL stored procedure, how to create stored procedure in SQL, syntax of SQL stored procedure, overview of temp table in SQL, what are the ways to create temp table in SQL with syntax, SQL store result of stored procedure into temp table with parameter and without parameter explain with practical examples.

 

References

Insert results of a stored procedure into a temporary table

 

Read More

SQL select
SQL stored procedure