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?
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.
2000 and I've linked to the 2000 documentation saying it's not available.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 
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
@q variable. Nice try but no dice.
CREATE TEMP TABLE(I'm sure you can insert into a temp table from EXEC).