Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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
@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