Load all tables in database dynamically

[jagan]

Below script helps in loading all the tables in the database and store that tables in QVDs.

ODBC CONNECT TO MyODBC;

SQLTableList:

LOAD "TABLE_NAME" as  TableNames;

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Note:

Information schema.columns is for MS SQL Server database, you can replace that with the euqivalent table in the corresponding database. 

For oracle ALL_TABLES   ALL_TABLES

For MYSQL - all_tables or SHOW_TABLES

How to List All Tables and Describe Tables in Oracle, MySQL, DB2 and PostgreSQL | OneWebSQL

MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.37 SHOW TABLES Syntax

Let vTableCount = NoOfRows('SQLTableList');

Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';

For i = 0 To $(vTableCount) -1

LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';

LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

$(vMyTableNameQVD):

SQL SELECT *FROM $(vMyTableName);

STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

DROP Table $(vMyTableNameQVD);

Next i

Hope this helps others.

Regards,

Jagan.

[Ildarv]

Hello Jagan. Could u help me? 

I used this code 

SQLTableList:
LOAD "TABLE_NAME" as TableNames;
SQL SELECT DISTINCT "TABLE_NAME"
FROM "INFORMATION_SCHEMA".COLUMNS;

Let vTableCount = NoOfRows('SQLTableList');
Let vTableCountQVD = 'TEST.dbo.[' & NoOfRows('SQLTableList') & ']';
For i = 0 To $(vTableCount) -1
LET vMyTableName = 'TEST.dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';
LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

GeneralLedger:
LOAD Период,
"Регистратор_Тип",
"Регистратор_Номер",
"Регистратор_Дата",
НомерСтроки,
Активность,
Сумма,
"СчетДт_Наименование",
"СчетДт_Код",
"СчетКт_Наименование",
"СчетКт_Код";

$(vMyTableNameQVD):
SQL SELECT *FROM $(vMyTableName);
STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;
DROP Table $(vMyTableNameQVD);
Next i;

But i have 1 error when store into qvd file. 

SQL Table List << COLUMNS
Selected lines: 3 902
GeneralLedger_2832 << SELECT *from TEST.dbo.[GeneralLedger_2832]
Selected rows: 100
The following error occurred:
No qualified path for file: ***
The error occurred here:
STORE General Ledger_2832 INTO General Ledger_2832.QVD
No data loaded. Fix the error and reload.

What i did wrong? 

[AnkitMadhukar]

Hi @jagan ,

Thank you for this wonderful script!!

I am using it for my current data load , but it only fetches records for one table and then the loop ends.
I checked the value of vTableCount and it reflects the correct count but the scipt on running loads only one table.

Has anyone else faced this issue.

Best Regards,
Ankit Madhukar

[covenant_bi]

@AnkitMadhukar  did you copy paste the Next i from the initial script that jagan posted?  It is kind of tucked away and doesn't look like part of the script.  I know this because I did the same thing.  I added NEXT i; and it took off.  hth