Dynamic insert into variable table statement SQL Server

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.

---

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

---

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

---

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

---

To create dynamic insert query it is really a task, I also struggle to find it ,finally I have tried in the following way and it's successfully working. Please find the code below.

CREATE PROCEDURE [dbo].[InsertTodaysData] (@tbl varchar(50),@Days int,
 @MDate varchar(50), @EValue varchar(50), @Speed varchar(50),
@Totalreturn varchar(50),@Closingv varchar(50), @TotalReturnV varchar(50))

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SQLQuery varchar(2000)
-- Insert statements for procedure here
set @SQLQuery = 'INSERT INTO '+@tbl+' (ID,MDate,EValue,Speed,TotalReturnRatio,ClosingValue,
TotalReturnValue) VALUES ('+@Days+','''+@MDate+''', '+@EValue+', '+@Speed+',
 '+@Totalreturn+', '+@Closingv+', '+@TotalReturnV+')'

EXECUTE(@SQLQuery)

END

Hope this will help you..