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

Question

I am 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 through comments) that this works on SQL Server 2000, but may not be so simple.

It was a bit complicated until 2008 - as it turns out in SQL Server 2008, this can be done easily.

and comment:

This is not a function of 2008. it was there as i remember ... from sql server 2000? In any case, this is a great feature!

How can I quickly accomplish this for SQL Server 2000?

Using the code on which the article is based, I get 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 found https://stackoverflow.com/a/166268/ and it also supports the concept that this can be done in SQL Server 2000, but this post was supposed to address SQL Server 2005, and that’s not the case. "Enter 2000 a lot.

Answer 1

You can do this in SQL Server starting in 2005. Although SQL Server 2000 supports table variables, it does not support INSERT with EXECUTE for a table variable. The most suitable alternative is to use a temporary table.

Instead of using a table variable such as @mytable , use a table named #mytable , and you can INSERT use the exec stored procedure. First you need to create a temporary table using the create table command.

Answer 2

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

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