Execute a SQL String and insert result into table

Question

I have a table

DECLARE @Results TABLE(QueryIndex smallint, FieldValue nvarchar(50))

QueryIndex is a loop counter, it will get value from @QueryIndex. FieldValue will get value from another SQL String. For some reason, I need to execute SQL string dynamically.

SET @SQL = "SELECT " + @FieldName + " FROM MyView"
            + " WHERE Condition1='" + @Value1 + "'"
            + " AND Condition2=" + CONVERT(nvarchar(12),@Value2)

Now I try to insert data into @Results

NSERT INTO @Results(QueryIndex, FieldValue)
SELECT @QueryIndex, EXEC (@SQL)

No surprise, this code won't work. Please provide me solution to insert data into my table. Any methods will be worth trying. The result in table should like this:

QueryIndex   FieldName
  1            First
  2            Second
  3            Third

Thanks.

Alex K. · Accepted Answer · 2012-05-02 10:46:23Z

3

You need to combine the retrieval of @QueryIndex with the select then you can simply

SET @SQL = 'SELECT ' + cast(@QueryIndex as varchar(16)) + ',' + @FieldName + ...
INSERT INTO @Results(QueryIndex, FieldValue)
   EXEC (@SQL)

answered May 2, 2012 at 10:46

Alex K.

177k32 gold badges276 silver badges299 bronze badges

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

Comments

Royi Namir · Accepted Answer · 2012-05-02 10:40:29Z

1

you should create the structure of the containing table - before !

 CREATE TABLE #tmp  // or @tbl... doesnt really matter...
      ( 
        ...
      ) 

    INSERT INTO #tmp  -- if we use exec we  must have the pre-Structure of the table
    EXEC (@sql)

now , Do waht ever you want to do with #tmp....

answered May 2, 2012 at 10:40

Royi Namir

149k145 gold badges502 silver badges833 bronze badges

2 Comments

Aaron Bertrand Over a year ago

#tmp or @tbl may matter - @tmp couldn't always be the target of insert / exec (though I honestly forget when that changed).

Shinigamae Over a year ago

@Royi Namir thanks for suggesting. But I think it is not solution for my case. As Alex's solution, I just want to insert 2 values at the same time. There's not much different between using temp table and using var table here :)

Collectives™ on Stack Overflow

Execute a SQL String and insert result into table

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related