Member Avatar for carobee

Hi,

I have created a temporary @table and while inserting ,i am using dynamic sql.But this is not getting executed and throws an error that
"EXECUTE cannot be used as a source when inserting into a table variable"
i am using SQLServer2005.
Dont know where i am going wrong

DECLARE @SqlQuery NVARCHAR(4000);
	SELECT @SqlQuery='SELECT TOP '+CAST(@no_of_rows as CHAR)+'tblEmployee.id   ,
			tblProject.ID, 
			0,
			0  			
	FROM         tblBilling (NOLOCK) '
INSERT @table 
EXEC @SqlQuery
  • 3 Contributors
  • 6 Replies
  • 2K Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by jbisono

Dani AI

Generated

You are running into a SQL Server 2005 limitation: you cannot use INSERT ... EXEC/EXECUTE to feed rows into a table variable. That is why your dynamic SQL fails even though the same pattern works with a #temp table (as showed). You do not need dynamic SQL here anyway. The only reason you built the string was to inject a TOP value.

A cleaner fix is to keep the target as a table variable and parameterize TOP. This avoids EXEC, respects the table variable, and is easier to reason about than SET ROWCOUNT (which is session-scoped and can surprise later statements). Also add an ORDER BY so the TOP selection is deterministic.

-- Assume @table already exists with the proper schema and @no_of_rows is INT
INSERT INTO @table (employeeid, projectid, num1, num2)
SELECT TOP (@no_of_rows)
       e.id,
       p.id,
       0,
       0
FROM dbo.tblBilling AS b WITH (NOLOCK)
JOIN dbo.tblEmployee AS e ON e.EmployeeID = b.EmployeeID
JOIN dbo.tblProject  AS p ON p.ProjectID  = b.ProjectID
ORDER BY e.id;  -- choose a deterministic sort key for your use case

Notes:

  • Prefer TOP(@n) over SET ROWCOUNT, which used as a workaround; ROWCOUNT can affect unrelated queries in the same session if not reset.
  • Always specify the target column list on INSERT so types line up and future schema changes do not break the insert.
  • If you truly must use dynamic SQL for other reasons, capture into a #temp table inside the dynamic batch, then insert from #temp into your table variable in a second statement. This keeps the table variable in play without violating the INSERT ... EXEC restriction.
  • Be cautious with NOLOCK; it can return duplicate or missing rows under load. Consider adding the proper isolation level if correctness matters.

Recommended Answers

All 6 Replies

Member Avatar for jbisono

hi carobee try this

DECLARE @SqlQuery NVARCHAR(4000);
    set @SqlQuery='You query here'
    INSERT into @table
    exec(@SqlQuery)

regards

Member Avatar for carobee

The error i am getting is "EXECUTE cannot be used as a source when inserting into a table variable"

Member Avatar for jbisono

well that is true @table does not have a structure to handle the insert. I guess you are using temporary tables which you can reference like this

create table #TABLE(
employeeid varchar(10),
projectid varchar(10),
num1 int,
num2 int
)
      DECLARE @SqlQuery NVARCHAR(4000);
      SET @SqlQuery='SELECT TOP '+CAST(@no_of_rows AS CHAR)+'tblEmployee.id ,
      tblProject.ID, 0,0
      FROM tblBilling (NOLOCK) '
      INSERT INTO #TABLE
      EXEC(@SqlQuery)

maybe that is what you are looking for. regards.

Edited by jbisono because: n/a
Member Avatar for padtes

I this was already solved, please mark so. Otherwise here is variation of the same:

CREATE TABLE #TABLE (
employeeid VARCHAR(10),
projectid VARCHAR(10)
,num1 INT
,num2 INT
)

DECLARE @SqlQuery NVARCHAR(4000);

SET @SqlQuery=' INSERT INTO #TABLE SELECT TOP '+CAST(@no_of_rows AS CHAR)+'tblEmployee.id ,
      tblProject.ID, 0,0
      FROM tblBilling (NOLOCK) '
      
      EXEC(@SqlQuery)

please close thread if this or earlier solution has worked

Edited by padtes because: n/a
Member Avatar for carobee

guys thats a table variable and not a temporary table
well i found the solution

SET ROWCOUNT @no
INSERT @table
Select  tblEmployee.id,
            tblProject.ID,
           0,
           0
FROM         tblBilling (NOLOCK)

SET ROWCOUNT 0

@no can be configured

Member Avatar for jbisono

Ok cool to know.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.