In this article we will show you, How to write a SQL Query to Insert Stored Procedure result into Temporary Table in SQL Server with example. This is one of the common SQL Interview Question that you might face in the interviews. For this example, We are going to use the below shown data
TIP: I suggest you to refer both the Stored Procedure, and Temporary Table articles to get the basic idea
Data present in the Employee table inside our SQL Tutorial database.
In this example we are going to use the below shown Stored procedure that will SELECT all the records from Employe table
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- SQL Stored Procedure - Insert Stored Procedure result into Temporary Table in SQL Example USE [SQL Tutorial] GO IF OBJECT_ID ( 'SelectStoredProcedureFirstExample', 'P' ) IS NOT NULL DROP PROCEDURE SelectStoredProcedureFirstExample; GO CREATE PROCEDURE SelectStoredProcedureFirstExample AS BEGIN SET NOCOUNT ON; SELECT [FirstName] + ' ' + [LastName] AS [Full Name] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [Employee] END GO |
OUTPUT
Insert Stored Procedure result into Temporary Table in SQL Example 1
In this example we will create a Temporary Table, and then we are going to use the INSERT INTO SELECT Statement to insert the Stored Procedure result into Temporary Table
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- SQL Stored Procedure - Insert Stored Procedure result into Temporary Table in SQL Example -- Creating Temporary Table CREATE TABLE #Employee( [FullName] [nvarchar](650) NULL, [Education] [nvarchar](255) NULL, [Occupation] [nvarchar](255) NULL, [YearlyIncome] [float] NULL, [Sales] [float] NULL, [HireDate] [datetime] NULL ) GO --Inserting Records from Stored procedure to Temporary Table INSERT INTO #Employee EXEC SelectStoredProcedureFirstExample GO -- Selecting Records from Temporary Table SELECT * FROM #Employee |
OUTPUT
Insert Stored Procedure result into Temporary Table in SQL Example 2
In this example we are going to use the SELECT INTO Statement, and OPENROWSET to insert the Stored Procedure result into Temporary Table
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- SQL Stored Procedure - Insert Stored Procedure results into Temporary Table in SQL Example sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; GO --Inserting Records from Stored procedure to Temporary Table SELECT * INTO #Employee FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', 'EXEC [SQL Tutorial].dbo.SelectStoredProcedureFirstExample') -- Selecting Records from Temporary Table SELECT * FROM #Employee |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply