topic Re: Load tables from sql in App Development

Load tables from sql

paulwalker — Fri, 23 May 2025 12:30:34 GMT

Experts!

I'm loading multiple tables at once using for loop and working fine, below is the my script.

SQL USE Test;

ListOfTables:

LOAD

RowNo() AS SrNo,

TableName

FROM

[Tables.xlsx]

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD

MAX(SrNo) AS NoOfTables

RESIDENT ListOfTables;

LET vNoOfTables= FieldValue('NoOfTables',1);

DROP TABLE Temp;

FOR i=1 to $(vNoOfTables)

LET vTable=FieldValue('TableName',$(i));

$(vTableName):

SQL SELECT * FROM $(vTable);

STORE [$(vTable)] INTO $(vTable).QVD (QVD);

DROP TABLE [$(vTable)];

NEXT i

but my users are asking to load only required columns, is there any possibility ?

Thanks in Advance!

Re: Load tables from sql

MatheusC — Fri, 23 May 2025 13:02:36 GMT

@paulwalker

using SELECT with the * character.
This will return all existing fields in the SQL table.

Simply insert the required fields by removing the character.

SQL SELECT
FieldA,
FieldB,
FieldC
FROM $(vTable);

- Matheus

Re: Load tables from sql

paulwalker — Fri, 23 May 2025 13:14:07 GMT

fields should be automated..

let say Category Table has 3 fields and Product table has 10 fields.

If I keep all the column names it will through an error.

Re: Load tables from sql

marcus_sommer — Fri, 23 May 2025 13:17:33 GMT

Yes, it's possible to apply something like this:

$(vTableName):

SQL SELECT $(vFields) FROM $(vTable) where $(vMyConditions);

The information must only be loaded in some way and then assigned to the variables. In your case maybe just added within the table-list Excel, like:

Table Fields Conditions
A F1, F2 Year >= 2024
B F1, F3 1 = 1

but there are also other ways like providing the information as rows and using concat() aggregations to create the lists. Depending on the data-base and their syntax-rules it might need some efforts to differentiate between fields, functions, numbers and strings to apply the right quote/bracket-wrapping. Because of the fact that there are duplicates possible within the fields or conditions you should switch the query from fieldvalue() to peek().

Re: Load tables from sql

QFabian — Fri, 23 May 2025 13:36:24 GMT

Hi @paulwalker , one thing is creating the QVDs, that usually store all the columns, and then users, or in a users App, you load just the required fields.

Re: Load tables from sql

paulwalker — Fri, 23 May 2025 15:09:14 GMT

Yes, your correct.

but It is taking more time to load all the tables into QVD's (Many tables have description kind of data and QVD's size also it's huge like showing GB's).

Just this is my thought, if I create like below structure (Required column is like flag, 1 should be required column and 0 we don't require - in future if we want any column just change 0 to 1)

Re: Load tables from sql

marcus_sommer — Fri, 23 May 2025 16:20:22 GMT

Such structure might be loaded per:

t: load Table, concat(Field, ',') as Fields from XLSX where Required = 1 group by Table;

for i = 0 to noofrows('t') - 1
let t = peek('Table', $(i), 't');
let f = peek('Fields', $(i), 't');
...

and if the data-set are really huge you may add like above hinted appropriate where-clauses and/or extending the logic by implementing an incremental approach, for example storing the max. date from each load in a variable and then only loading newer data and adding them to the historic ones. There are a lot of possibilities to reduce the workload of the data-base - of course by increasing the complexity ...