Dynamically insert a SQL Server variable table into a statement

Question

I have a table of variables:

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

I want to make dynamic sql, so I insert some data into this table (everything inside the 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 will look like this every time:

 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 doing this: So, I 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 this syntax for dynamically inserting variables into a table?

OK, I get that.

Answer 1

You have a table variable, not a variable containing the table name.

So, you will 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 also need to declare the table variable as an instruction inside the @sql variable, and also execute the declaration table and insert it together or use a local / global temporary table.

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

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

Some good info on temporary tables in BOL

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

And type table .

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

Answer 2

You can use the INSERT ... EXEC syntax to insert data returned by a dynamic SELECT. Of course, you will 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 3

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

Answer 4

To create a dynamic insert request, this is really a task, I am also trying to find it, finally I tried as follows and worked successfully. 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 helps you.