49

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
XMozart
  • 879
  • 2
  • 10
  • 22
  • If it's true, you should `CREATE TEMP TABLE` (I'm sure you can insert into a temp table from EXEC). – Anton Kovalenko Jan 31 '13 at 09:59
  • "Is it true?" - [yes](http://msdn.microsoft.com/en-us/library/aa260638(v=sql.80).aspx) - "However, table may not be used in the following statements: INSERT INTO table_variable EXEC stored_procedure" – Damien_The_Unbeliever Jan 31 '13 at 10:12
  • Ok, i got it,, it's like @Anton Kovalenko said,, i need to create temporary table , not Table variable ... Thx. :) – XMozart Jan 31 '13 at 10:15

3 Answers3

82

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.

Community
  • 1
  • 1
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 1
    @Damien, in your example there is no reason to use a Temp Table, you can still use a Table variable even in your example. – GoldBishop Feb 26 '14 at 16:02
  • 2
    Verified that you can use @tableVariables in 2008+ – ohmusama Aug 25 '14 at 21:50
  • 3
    @ohmusama - yes, but the question is tagged `2000` and I've linked to the 2000 documentation saying it's not available. – Damien_The_Unbeliever Aug 26 '14 at 07:41
  • @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. – ohmusama Aug 26 '14 at 19:14
  • 1
    @ohmusama - 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. – Damien_The_Unbeliever Aug 27 '14 at 05:36
  • @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. – ohmusama Aug 27 '14 at 18:37
14

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
Be Kind To New Users
  • 9,672
  • 13
  • 78
  • 125
cob666
  • 151
  • 1
  • 2
5
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

Sandip Bantawa
  • 2,822
  • 4
  • 31
  • 47