INSERT INTO @TABLE EXEC @query with SQL Server 2000

Question

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

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?

Incommensurate

If it's true, you should CREATE TEMP TABLE (I'm sure you can insert into a temp table from EXEC). – Cavil

"Is it true?" - yes - "However, table may not be used in the following statements: INSERT INTO table_variable EXEC stored_procedure" – Medorra

Ok, i got it,, it's like @Anton Kovalenko said,, i need to create temporary table , not Table variable ... Thx. :) – Incommensurate

Answer 1

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.

Medorra

@Damien, in your example there is no reason to use a Temp Table, you can still use a Table variable even in your example. – Carilyn

Verified that you can use @tableVariables in 2008+ – Tangelatangelo

@Tangelatangelo - yes, but the question is tagged 2000 and I've linked to the 2000 documentation saying it's not available. – Medorra

@Damien_The_Unbeliever, I know, but since google can't figure that out, (as I got here from there), this is a comment for other people like me. – Tangelatangelo

@Tangelatangelo - okay, I've added an note at the top to (try to) make it clear that this question and answer do just relate to the 2000 version of the product. – Medorra

@Damien_The_Unbeliever, I just wanted to leave a comment for others. In no way was your answer wrong or needed changing. That wasn't my objective. But okay. – Tangelatangelo

Answer 2

The documentation is misleading.
I have the following code running in production

DECLARE @table TABLE (UserID varchar(100))
DECLARE @sql varchar(1000)
SET @sql = 'spSelUserIDList'
/* Will also work
   SET @sql = 'SELECT UserID FROM UserTable'
*/

INSERT INTO @table
EXEC(@sql)

SELECT * FROM @table

Rigney

Answer 3

DECLARE @q nvarchar(4000)
SET @q = 'DECLARE @tmp TABLE (code VARCHAR(50), mount MONEY)
INSERT INTO @tmp
  (
    code,
    mount
  )
SELECT coa_code,
       amount
FROM   T_Ledger_detail

SELECT *
FROM   @tmp'

EXEC sp_executesql @q

If you want in dynamic query

Contradictory

yes , i want the values is from dynamic script.. and i think this is not dynamic.. – Incommensurate

So above query will help you – Contradictory

Yeah this is a static execution. In order for this to be dynamic you need to have concatenations within the assignment to the @q variable. Nice try but no dice. – Carilyn