Search code examples
sql-serversql-server-2005stored-procedures

insert return data from SP to temp table

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?

Solution

  • 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.