Inserting into temporary table by dynamic query

[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

[jbisono]

hi carobee try this

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

regards

[carobee]

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

[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.

[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

[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

[jbisono]

Ok cool to know.