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?
This question is related to
sql-server
tsql
sql-server-2000
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
Source: Stackoverflow.com