topic I need a loop for importing multiple DBs in SQL Server in QlikView

I need a loop for importing multiple DBs in SQL Server

Wed, 15 Feb 2012 09:22:58 GMT

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;

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

hic — Wed, 15 Feb 2012 09:33:06 GMT

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

I need a loop for importing multiple DBs in SQL Server

Thu, 23 Feb 2012 18:56:23 GMT

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

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

fernando_tonial — Thu, 23 Feb 2012 19:53:13 GMT

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

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

Thu, 23 Feb 2012 21:02:46 GMT

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