Dynamic insert into variable table statement SQL Server

Question

I have a variable table:

DECLARE @A_Table TABLE(ID INT, att1 VARCHAR(100), att2 nvarchar(200))

I want to make dynamic sql, so I insert into this table some data (all inside a loop):

WHILE (@i <= 100) BEGIN         
  SELECT @other_att  = NAME  FROM @other_Table where ID =  @i;
  SET @sql = 'INSERT ' + @A_Table+ '(ID,att1,att2) SELECT '+CAST(@i AS VARCHAR)+' , '''+ @other_att+''', SUM('+ @other_att') FROM '+ @EVEN_OTHER_Table;
EXEC (@sql);

END 

sql every time would look like:

INSERT INTO @A_Table SELECT 1 , 'subject', SUM(subject)
INSERT INTO @A_Table SELECT 2 , 'age', SUM(age)
INSERT INTO @A_Table SELECT 3 , 'sex', SUM(sex).... 

AND after executing this : SO I will get:

@A_Table:
id    att1   att2
1   subject   4.3
2   age       4.5
3   sex       4.1

but I get an error:

Msg 137, Level 16, State 1, Line 48
Must declare the scalar variable "@A_Table".

SO what is it the syntax to insert dynamically into a variable table?

Ok I have understood it.

Answer 1

You could use the INSERT ... EXEC syntax to insert the data returned by the dynamic SELECT. Of course, you would then need to remove the INSERT part from the dynamic statement.

WHILE (@i <= 100) BEGIN         
  SELECT @other_att  = NAME  FROM @other_Table where ID =  @i;
  SET @sql = 'SELECT '+CAST(@i AS VARCHAR)+' , ''' + @other_att+''', SUM('+ @other_att + ') FROM '+ @EVEN_OTHER_Table;
  INSERT INTO @A_Table (ID,att1,att2)
    EXEC (@sql);
END

Answer 2

Your EXEC statement occurs in a different context and is therefore unaware of any variables created in your original context.

Answer 3

You have a table variable, not a variable that contains the table name.

So you would need the following.

WHILE (@i <= 100) BEGIN         
  SELECT @other_att  = NAME  FROM @other_Table where ID =  @i;
  SET @sql = 'INSERT INTO @A_Table (ID,att1,att2) SELECT '+CAST(@i AS VARCHAR)+' , '''+ @other_att+''', SUM('+ @other_att') FROM @EVEN_OTHER_Table';
EXEC (@sql);

END 

You would also need to declare the table variable as a statement inside the @sql variable, and execute your declare table and inserts together, or use a local/global temporary table.

With a local temporary table (stored in the tempdb) you could do something like this.

CREATE TABLE #testtbl (ID INT);
EXEC ('INSERT INTO #testtbl VALUES (1)');
SELECT * FROM #testtbl
DROP TABLE #testtbl

Some good info about temporary tables in BOL

http://msdn.microsoft.com/en-us/library/ms174979.aspx - quite far down the page

And the table type.

http://msdn.microsoft.com/en-us/library/ms175010.aspx