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.

Andriy M · Accepted Answer · 2014-06-23 15:25:49Z

12

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

edited Jun 23, 2014 at 15:25

answered Mar 4, 2011 at 17:35

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

edgarmtze Over a year ago

In this case Is It is more efficient to use sp_execute?

Andriy M Over a year ago

@darkcminor: A quick look through the results of googling for exec vs sp_executesql didn't reveal to me any particular advantages of EXEC() over sp_executesql in what I would call ordinary cases, to which I would include yours as well. I'm sorry, I cannot tell you much about that, but some people have certainly studied the matter more thoroughly.

Chris Diver · Accepted Answer · 2011-03-04 16:41:29Z

6

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

edited Mar 4, 2011 at 16:41

answered Mar 4, 2011 at 16:26

Chris Diver

19.9k4 gold badges50 silver badges58 bronze badges

3 Comments

edgarmtze Over a year ago

It keeps saying that @A_Table is not declared, but its is... ` Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@A_Table".`

edgarmtze Over a year ago

How is @sql declared, Do you recommend using a temporal table?

Chris Diver Over a year ago

that's because @A_Table is not in the same scope, it depends what you are trying to achieve but you could add the statements to declare your variable and inserts inside @sql and then execute (@sql), but you may want a temporary table instead. Which will allow it to be declared in a different scope sqlteam.com/article/temporary-tables

Joe Stefanelli · Accepted Answer · 2011-03-04 16:28:15Z

4

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

answered Mar 4, 2011 at 16:28

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Comments

Sapnandu · Accepted Answer · 2017-11-26 17:49:36Z

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

Collectives™ on Stack Overflow

Dynamic insert into variable table statement SQL Server

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related