Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

I need a loop for importing multiple DBs in SQL Server

Below is a simplification of what I'm trying to do. My XXX table would be a sql statement for a master list of all DBs.  I would cycle through that and concatenate a master table.  Can someone please help me!!!!
XXX:
LOAD * Inline [X
Sales.BiZ1
Sales.BiZ2
Sales.BiZ3
........];

OLEDB CONNECT TO [Provider=.....
for each x in XXX
SQL SELECT * FROM [InsideTSQL2008].$(x);
next;
1 Solution

Accepted Solutions

Re: I need a loop for importing multiple DBs in SQL Server

For vTableNo = 1 to noOfRows('XXX')
Let vTable = peek('X',vTableNo-1,'XXX');
Select * from [$(vTable)] ;
Next vTableNo

View solution in original post

4 Replies

Re: I need a loop for importing multiple DBs in SQL Server

For vTableNo = 1 to noOfRows('XXX')
Let vTable = peek('X',vTableNo-1,'XXX');
Select * from [$(vTable)] ;
Next vTableNo

View solution in original post

Not applicable

I need a loop for importing multiple DBs in SQL Server

Thank you Henric.  This was extremely helpful.  I appreciate you taking the time to share this with me!

Partner
Partner

Re: I need a loop for importing multiple DBs in SQL Server

Hello, try this script.

LET vOwner = 'TONIAL';

OLEDB CONNECT32 TO [Provider=MSDAORA.1;User ID=$(vOwner);Data Source=XE] (XPassword is SXRQVYRNJbaEDYEGRJBA);

Tabs:

SQLTables;

TABLES:

LOAD TABLE_SCHEMA&'.'&TABLE_NAME AS TN,*

Resident Tabs

Where TABLE_TYPE ='TABLE'

  and not WildMatch(TABLE_NAME, 'APEX*')

  and TABLE_SCHEMA = Upper('$(vOwner)');

DROP Table Tabs;

FOR i = 0 to NoOfRows('TABLES');

          LET vTableName = Peek('TABLE_NAME', $(i), 'TABLES');

          TRACE $(vTableName);

          $(vTableName):

          SQL SELECT *

          From $(vTableName);

          LET vNoOfRows = NoOfRows('$(vTableName)');

          IF $(vNoOfRows) >0 THEN

                    TRACE STORE TABLE $(vTableName);

                    STORE $(vTableName) INTO $(vTableName).QVD;

          ENDIF

          DROP Table $(vTableName);

NEXT

Not applicable

Re: I need a loop for importing multiple DBs in SQL Server

This is close to what I ended up with.  Excellent example.  Thank you for your time and effort.