-1

I have two tables:

  • Table A contains the columns ID and Query.
  • Table B contains the columns ID, Value and Category.

In every row of table A (without any exceptions) the column Query contains an SQL query as nvarchar, which looks something like SELECT * FROM B WHERE Category = 1.

I cannot figure out, how i can execute all Queries of table A and merge those results and return them (inside a select statement).

I already tried to iterate through all rows in table A, inserting the results of EXEC(Query) into a temporary table #C and finally execute SELECT * FROM #C. But i was not successful with this solution.

EDIT

Just for clarification: The columns and their data types of the resultsets of the queries - stored in table A - are identical. Table B just has a column with datatype [timestamp]. The exception i receive is:

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column

ˈvɔlə
  • 9,204
  • 10
  • 63
  • 89
  • You've provided a link to vintage documentation for `timestamp`. Have you read it? The value is supplied by the database and provides a `rowversion` for each row, not a date/time. ([This](http://stackoverflow.com/questions/4460197/what-is-difference-between-datetime-and-timestamp) SO question addresses the differences between `timestamp` and `datetime`.) – HABO May 12 '17 at 13:30
  • It sounds like you just want to do a dynamic sql statement where you concatenate all the Select statements from table A with a UNION ALL in between each Select statement. – Anthony Hancock May 12 '17 at 14:08

2 Answers2

1

This is too long for a comment.

You can run all the queries using a T-SQL statement. That is, a WHILE loop with a cursor or similar mechanism to run each query using dynamic SQL.

Putting the results into a single table is problematic. All the queries would need to return the same columns in the same order with the same types. It doesn't look to me like SELECT * FROM B and SELECT * FROM #C would meet these conditions.

You probably need to think more about what you really need to accomplish.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think this should get you what you want if I understand the question.

DECLARE @sqlStatement NVARCHAR(MAX) = '';

SELECT @sqlStatement += CONCAT(Query,' UNION ALL ')
FROM TableA
;

SET @sqlStatement = LEFT(@sqlStatement, LEN(@sqlStatement) - 10 );

EXEC sp_executesql @sqlStatement;
Anthony Hancock
  • 911
  • 6
  • 13