Dynamic Query Need

Ok, so I have a clue but not enough focus to find the final answer here.  So . . . .

I have a query that goes something like:
select ResultName
 from MyDB
 where name like 'xyz%'

In the above, there could be 1 result or there could be 20 based on when I run the query.

I need a loop that will allow me to run a query such as:

select * into #tempResults
from (
  select 'ResultName', OtherItem
  from ResultName

I need this to run and insert into a temp table one time for each result of the first query.

My apologies in advance for the messy way I presented this.  I'm pretty sure I can adapt once I get an example.  I'm using DB2 for i series, but I'm sure I could adapt it from MS SQL or anything similar.

Thanks for your assistance!
LVL 11
RayData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
select ResultName
into #tempResults
from MyDB
 where name like 'xyz%'

Open in new window

Kent OlsenDBACommented:
Hi Ray,

What do you want to do with the results?  For most uses you should be able to structure a query so that the number of results is irrelevant.

For the unusual case where you do need to manage the data one row at a time a cursor will do fine.

RayData AnalystAuthor Commented:
KDO - The results of the first query are only to get session/table names to be used in the second query.  So I will need to run a query that takes results from q1.result1 query into a temp, then q1.result2 query into the same temp and so on until each session/table from the original query has been run in the secondary query (resultX).

eghtebas - that is simply inserting into a temp table, that part I have figured out.  I need help with the LOOP part so I can run the query INTO the temp table one time for each result from another query.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Kent OlsenDBACommented:
Perhaps something like this:

DECLARE c1 CURSOR FOR SELECT name FROM mytable WHERE {condition};
DECLARE statement VARCHAR(500:

  FETCH C1 INTO vname;
  SET statement = 'INSERT INTO TEMP SELECT * FROM ' || vname;

Open in new window

Dave FordSoftware Developer / Database AdministratorCommented:
Personally, I would do it in a stored procedure ... a little something like this. HTH!

CREATE PROCEDURE MySchema.CountEm (IN p_startsWith VARCHAR(10))
set option dbgview = *source


  DECLARE v_notfound INT;
  DECLARE v_tablename VARCHAR(100);
  DECLARE v_schemaname VARCHAR(10);
  DECLARE v_sql CHAR(256);

    select TABLE_SCHEMA,
      from qsys2.systables
     where table_schema = 'SomeSchema'
       and table_name like p_startsWith || '%';

    SET v_notfound = 1;

  DELETE from MySchema.TableCount with nc;

  SET v_notfound = 0;
  OPEN c_tables;
  FETCH c_tables INTO v_schemaName, v_tableName;  
  WHILE v_notfound = 0 DO

    SET v_sql =
      'INSERT INTO MySchema.TableCount  select ''' ||
      v_tablename || 
      ''', count(*) ' ||
      'FROM ' ||
      v_schemaname || '.' || v_tablename;

    PREPARE v_statement FROM v_sql;
    EXECUTE v_statement;

    FETCH c_tables INTO v_schemaName, v_tableName;
  CLOSE c_tables;


Open in new window

RayData AnalystAuthor Commented:
Dave, I don't have the privileges to add an SP :-(

Kdo, I'll try in the morning.

Any other posts I'll look at them as well.

Thanks you everyone for your efforts so far !!
Kent OlsenDBACommented:
Hi Ray,

The code that I posted was basically the core that would have to be put into a stored procedure.  Dave offered the complete code.

Can you live with generating the SQL in one query and running it in a second?  It would probably require some manual intervention to copy/past the results of the first query (which generates the SQL) or writing the results to a file and executing the file.

If so, the entire thing reduces to
SELECT 'INSERT INTO temp SELECT * FROM ' || name ||';' FROM mytable;

Open in new window

The output of that query will be the INSERT statements that will need to be run.

RayData AnalystAuthor Commented:
So while I was trying to figure this out, I also went down a path of letting Excel build my queries that it would eventually run and get results from anyway.  I've included a link to the problem I ran into there and how it was solved.  Thus, solving this problem.

You guys let me know how you feel points should be distributed as I didn't really use any of these ultimately, but this information did lead me down the path I eventually took (especially KDO's last comment inspired me).


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RayData AnalystAuthor Commented:
splitting the points between the two most helpful and that pushed me towards my final resolution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.