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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] INSERT INTO @TABLE EXEC @query with SQL Server 2000

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2000