从SQL Server 2000上的存储过程插入到临时表中 [英] Insert into a temp table from a stored procedure on Sql Server 2000

问题描述

我正在尝试完成此处描述的内容:

I'm trying to accomplish what is described here:

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

该文章说(在注释的支持下)，这在SQL Server 2000上有效，但可能并不那么容易.

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

在2008年之前，这有些棘手- 原来在SQL Server 2008中 可以轻松完成.

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

和一条评论指出:

这不是2008年的功能.在那里 因为我记得……来自sql server 2000?无论如何，这是一个很棒的功能！

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

如何为SQL Server 2000快速完成此任务?

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.

我确实找到了此Stackoverflow帖子，它还支持可以在SQL Server 2000中完成此操作的概念，但是这篇文章是针对SQL Server 2005的，它在2000年的使用并不多.

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.

推荐答案

您可以从2005版本开始在SQL Server中执行此操作.尽管SQL Server 2000支持表变量，但它不支持对表变量使用INSERT和EXECUTE.支持的最接近的替代方法是使用临时表.

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.

使用诸如#mytable之类的表代替使用诸如@mytable之类的表变量，您可以使用exec存储的proc insert.您确实需要先使用create 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.

这篇关于从SQL Server 2000上的存储过程插入到临时表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！