0

I need a query to insert data into temp table with columns (dbname, schemaname, tablename,query_id, select_cols, join_on, where_clause).

These are the cols I required and select_cols, join_on, where_clause columns must retreive from an XML query.

Eg: I have created two table and join them. Query is like:

select id, name, gender, marks, address
from #temp a 
inner join #temp1b on a.id=b.id

I need to insert into XML and then create table like below

So I need table like:

Dbname | schema |table       | queryid | select     | on ---colnames
Tempdb | dbo         |#temp    | 1            | id,name,..    | id ----values
Tempdb|dbo           | #temp2 |2             |address         |id

Can someone help me on this. Thanks in advance.

  • Hello, Could you please explain it better. As per my Understanding you have a select Query and it need to be created as XML and Inserted into a Table? – Annamalai D Mar 02 '21 at 06:36
  • For example: you have a select query and ran with execution plan . In execution plan when you select 'show execution plan XML.. ' you get an xml query with tags, from that query i need what are columns in select statement and if there r joining then what are joining columns and similarly where condition column in a single table. –  Mar 02 '21 at 06:50
  • It's not that easy to get that kind of info out of a query plan. For example, what would you do with `select a from t1 union all select b from t2` you now have two base tables, what about if there is no join, or multiple joins, or a CTE? What about no `from` clause? Or a filtered index is used which satisfies the `where`? Or parts of the query are removed due to algebrization? If you have a specific query plan XML already then sure we can look at it, please [edit] – Charlieface Mar 02 '21 at 07:17
  • @charlieface, if we dont have joins or where clause we can consider that column as empty for particular table, we need to reterive data for all tables.you can try with the query which i mentioned above or take a simple query from sys.query_store_plan –  Mar 02 '21 at 09:28
  • I don't think you understood my point. Another example is a correlated nested loop join, where there is no join condition as the inner seek sorts that out. An even better example: an indexed view matches a query on the base table. The table doesn't appear *at all* in the query plan. My point is that pretty much impossible to parse out such info from the query plan in the way you want, unless the query conforms to a pretty tight specification. – Charlieface Mar 02 '21 at 09:32

0 Answers0