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.
文章说(通过评论支持)这适用于SQL Server 2000但可能不那么容易。
This was a little tricky pre 2008 – as it turns out in SQL Server 2008 this can be done easily.
这在2008年之前有点棘手 - 事实证明,在SQL Server 2008中,这可以轻松完成。
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!
这不是2008年的特色。它是在那里,因为我记得...从SQL Server 2000?无论如何,这是一个很棒的功能!
How can I quickly accomplish this for SQL Server 2000?
如何快速完成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 * 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.
我确实找到了这个*帖子,它也支持这个可以在SQL Server 2000中完成的概念,但是这篇文章是为了解决SQL Server 2005而且它没有进入2000多。
2 个解决方案
#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.
您可以在2005年以后的SQL Server中执行此操作。虽然SQL Server 2000支持表变量,但它不支持INSERT和EXECUTE表变量。最受支持的替代方法是使用临时表。
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.
而不是使用像@mytable这样的表变量,使用一个名为#mytable的表,你可以使用exec存储过程插入。您需要首先使用create table命令创建临时表。
#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
#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.
您可以在2005年以后的SQL Server中执行此操作。虽然SQL Server 2000支持表变量,但它不支持INSERT和EXECUTE表变量。最受支持的替代方法是使用临时表。
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.
而不是使用像@mytable这样的表变量,使用一个名为#mytable的表,你可以使用exec存储过程插入。您需要首先使用create table命令创建临时表。
#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