Inserting Stored Procedure Results into a Temporary Table

Inserting Stored Procedure Results into a Temporary Table

Post by Carl Jaspe » Tue, 12 Aug 2003 15:15:25



Hi,
Is it possible to execute a Stored Procedure (sp_B) from within another
Stored Procedure (sp_A) and insert the results from sp_B into a temporary
table in sp_A?

Thanks,
Carl

 
 
 

Inserting Stored Procedure Results into a Temporary Table

Post by Keith Kratochvi » Tue, 12 Aug 2003 15:23:40


yes, however you need to create the temp table first and then perform an
INSERT INTO
EXEC
in order to load the table

--
Keith, SQL Server MVP


> Hi,
> Is it possible to execute a Stored Procedure (sp_B) from within another
> Stored Procedure (sp_A) and insert the results from sp_B into a temporary
> table in sp_A?

> Thanks,
> Carl


 
 
 

Inserting Stored Procedure Results into a Temporary Table

Post by Tom Morea » Tue, 12 Aug 2003 15:26:36


Yup:

create proc sp_A
as

create table #temp (x int)

insert #temp
exec sp_B
go

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql

Hi,
Is it possible to execute a Stored Procedure (sp_B) from within another
Stored Procedure (sp_A) and insert the results from sp_B into a temporary
table in sp_A?

Thanks,
Carl

 
 
 

Inserting Stored Procedure Results into a Temporary Table

Post by lindawi » Tue, 12 Aug 2003 15:29:20


Carl,

Quote:> Is it possible to execute a Stored Procedure (sp_B) from within
> another Stored Procedure (sp_A) and insert the results from sp_B
> into a temporary table in sp_A?

Yes, you can do that. Just create the table first.

create table #temp (c1 int ... )

insert #temp exec sp_b

Linda

 
 
 

Inserting Stored Procedure Results into a Temporary Table

Post by Carl Jaspe » Tue, 12 Aug 2003 15:23:42


Great. Thanks.



yes, however you need to create the temp table first and then perform an
INSERT INTO
EXEC
in order to load the table

--
Keith, SQL Server MVP


Quote:> Hi,
> Is it possible to execute a Stored Procedure (sp_B) from within another
> Stored Procedure (sp_A) and insert the results from sp_B into a temporary
> table in sp_A?

> Thanks,
> Carl

 
 
 

1. Inserting a stored procedure result set into a temporary table

I have run into a performance issue when trying to insert the result
set from a stored procedure into a temporary table.  I am running the
following statement.

create table  #temp
(field_1                char(80)        NULL,
 field_2                int             NULL,
 field_3                int             NULL,
 field_4                int             NULL,
 field_5                decimal(16, 2)  NULL)

insert into #temp
exec stored_procedure   param1, param2, param3

drop table #temp

When stored_procedure is executed and does not populate a table the
execution time is 11 seconds.  When the above script is run the
execution time is 45 seconds.  Does anyone know of performance issues
with inserting stored procedure result sets into tables?

2. Trouble Splitting out Distinct Rows into new Table...

3. Storing the result of a stored procedure call in a temporary table

4. Fetch & Efficiency

5. Insert procedure result to temporary table

6. VC 1.52 16-bit Jet Access

7. Results from stored procedure to temporary table?

8. MFC and ADO events

9. Creating a Temporary Table from a Stored Procedure Result Set

10. temporary tables in stored procedures/parameter views vs stored procedures

11. Inserting into table stored procedure result

12. Inserting result of query into ORDERED temporary table

13. Caching in a temporary table the result from a stored precedure