使用 SQL Server 2000 插入 @TABLE EXEC @query [英] INSERT INTO @TABLE EXEC @query with SQL Server 2000

问题描述

SQL Server 2000 是真的，不能用exec 插入表变量吗?

Is it true that SQL Server 2000, you can not insert into a table variable using exec?

我尝试了这个脚本并得到一个错误消息插入表变量时不能将EXECUTE用作源.

I tried this script and got an error message EXECUTE cannot be used as a source when inserting into a table variable.

declare @tmp TABLE (code varchar(50), mount money)
DECLARE @q nvarchar(4000)
SET @q = 'SELECT coa_code, amount FROM T_Ledger_detail'

INSERT INTO  @tmp (code, mount)
EXEC sp_executesql (@q)

SELECT * from @tmp

如果是这样，我该怎么办?

If that true, what should I do?

推荐答案

注意 - 此问题和答案与 SQL Server 2000 版本有关.在以后的版本中，取消了对 INSERT INTO @table_variable ... EXEC ... 的限制，因此它不适用于以后的版本.

N.B. - this question and answer relate to the 2000 version of SQL Server. In later versions, the restriction on INSERT INTO @table_variable ... EXEC ... were lifted and so it doesn't apply for those later versions.

您必须切换到临时表:

CREATE TABLE #tmp (code varchar(50), mount money)
DECLARE @q nvarchar(4000)
SET @q = 'SELECT coa_code, amount FROM T_Ledger_detail'

INSERT INTO  #tmp (code, mount)
EXEC sp_executesql (@q)

SELECT * from #tmp

---

来自文档:

表变量的行为类似于局部变量.它有一个明确定义的作用域，即在其中声明它的函数、存储过程或批处理.

A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.

在其范围内，表变量可以像普通表一样使用.它可以应用于在 SELECT、INSERT、UPDATE 和 DELETE 语句中使用表或表表达式的任何地方.但是，table 不能用于以下语句:

Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:

INSERT INTO table_variable EXEC stored_procedure

SELECT select_list INTO table_variable 语句.

这篇关于使用 SQL Server 2000 插入 @TABLE EXEC @query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！