insert return data from SP to temp table

Question

I have a stored proc, SP1, which executes and does select on one table. Now i have the need to insert that data in another table. I dont want to duplicate the code so i thought of inserting the data returned by the SP1 in the temp table so i can do some processing on it and save it.

I tried INSERT INTO #tmp; exec dbo.Sp1; but it gives me an error saying Invalid object name '#tmp'.. Isnt there a way i can create this table dynamically? Is there a better solution to this problem?

I was wrong with my previous answer. I recommand you take a look at this link: msdn.microsoft.com/en-us/library/Aa175921 — Francis P
– Francis P, Commented Mar 27, 2012 at 18:33

Russell Hart · Accepted Answer · 2012-03-27 18:31:42Z

1

The temp table has to exist before you can use insert into exec.

This is not such a draw back as it first seems as any changes to the procedure result set will likely brake your code.

answered Mar 27, 2012 at 18:31

Russell Hart

1,85113 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Asdfg Over a year ago

if my app uses one userid to connect to db, will that not create the problem as session will be shared to multiple users?

Russell Hart Over a year ago

no each connection will have a separate session and therefore separate temporary tables even with the same userid. A single # will be local to a session, double hash ## will be global to all users.

Diego · Accepted Answer · 2012-03-27 18:41:55Z

first run this:

create proc MySelect as begin select 1 as myColumn1, 2 as mycolumn2 end

and then this:

create table #tmp( col_1 int, col_2 int) insert into #tmp exec MySelect select * from #tmp

Kassabba · Accepted Answer · 2023-12-07 21:43:57Z

If you have control over dbo.sp1 then the easiest way is to use a temp table. Declare the temp table in the parent proc and use it in the child proc (without declaring it).

The table will maintain scope through both and you wont have to worry about multiple users running it since temp tables are local.

Collectives™ on Stack Overflow

insert return data from SP to temp table

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related