Insert into a temp table from a stored procedure on Sql Server 2000

Question

I'm trying to accomplish what is described here:

http://sqldev.wordpress.com/2008/05/06/insert-into-temporary-table-from-stored-procedure/

The article says (with support via comments) that this works on SQL sever 2000 but may not be as easy.

This was a little tricky pre 2008 – as it turns out in SQL Server 2008 this can be done easily.

and a comment states:

It’s not a 2008 feature. it was there since i remember… from sql server 2000? It’s a great feature anyway!

How can I quickly accomplish this for SQL Server 2000?

Using the code the article is based on, I'm getting the following error message:

Msg 197, Level 15, State 1, Line 7
EXECUTE cannot be used as a source when inserting into a table variable.

I did find this Stackoverflow post and it also supports the concept that this can be done in SQL Server 2000 but this post was to address SQL Server 2005 and it doesn't go in to 2000 much.

Solution 1

You can do this in SQL Server from 2005 version onwards. Although SQL Server 2000 supports table variables, it does not support INSERT with EXECUTE to a table variable. The closest supported alternative is to use a temp table.

Instead of using a table variable like @mytable, use a table called something like #mytable and you can insert using the exec stored proc. You do need to create the temp table first using the create table command.

Solution 2

Try using a real temp table instead of a table varaible...

CREATE TABLE #jobs
( jobcount int
)
INSERT
INTO #jobs
EXEC
sp_executesql
N’select 1 AS jobcount’
SELECT
*
FROM #jobs