Menu
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?
219
Create the EMP table which has a foreign key reference to the DEPT table. The foreign key will require that the DEPTNO in the EMP table exist in the DEPTNO column in the DEPT table. Table created. Insert row into DEPT table using named columns.
There are three ways to work around this error. The first option is to integrate the two stored procedures together into a single stored procedure. This option is possible if there are no other stored procedures, scripts or applications that are using either of the stored procedures to be merged.
There are two basic syntaxes of the INSERT INTO statement which are shown below. INSERT INTO TABLE_NAME (column1, column2, column3,... columnN) VALUES (value1, value2, value3,... valueN);
After creating the temp table, run sp_help on the temp table to get a list of the columns and data types including the size of varchar fields. I have an Excel sheet that I use to format the output of sp_help into a "create table" statement. You don't need anything that fancy, just copy and paste into your SQL editor.
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.
120
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
5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
