INSERT INTO @TABLE EXEC @query with SQL Server 2000

by

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.

You’ll have to switch to a temp table:

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

From the documentation:

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.

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 statements.

More Related Contents:

  1. Check if table exists in SQL Server
  2. How do I generate a random number for each row in a T-SQL select?
  3. Find index of last occurrence of a sub-string using T-SQL
  4. How can I insert binary file data into a binary SQL field using a simple insert statement?
  5. SQL Server – error when creating stored procedure
  6. How can foreign key constraints be temporarily disabled using T-SQL?
  7. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  8. How to change identity column values programmatically?
  9. SQL Server Regular expressions in T-SQL
  10. Maximum size of a varchar(max) variable
  11. best way to convert and validate a date string
  12. Is there a way to simplify a NULL compare of 2 values
  13. TSQL Try / Catch within Transaction or vice versa?
  14. What is the maximum number of characters that nvarchar(MAX) will hold?
  15. TSQL: Create a view that accesses multiple databases
  16. Sequence as default value for a column
  17. Aggregate bitwise-OR in a subquery
  18. How do I flush the PRINT buffer in TSQL?
  19. OR Operator Short-circuit in SQL Server
  20. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  21. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  22. How to change default language for SQL Server?
  23. T-SQL incrementing counter based on change in a column value
  24. T-SQL XOR Operator
  25. Joining tables from different servers
  26. Does query plan optimizer works well with joined/filtered table-valued functions?
  27. How to wait for 2 seconds?
  28. What is the meaning of “#” in front of a table name in TSQL?
  29. What are row, page and table locks? And when they are acquired?
  30. T-SQL for finding Redundant Indexes

Leave a Comment