Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Script for save tables as QVD files from many databases

Hi guys,

i'm new to QS and new here so please be forgiving for .

There you have script which i created:

LIB CONNECT TO 'mastermysql';

Databases:

LOAD "table_schema" as dbname;

SQL SELECT distinct(table_schema) FROM information_schema.tables where table_schema != 'information_schema';

LET DatabaseCount = NoOfRows('Databases');

FOR d = 0 TO $(DatabaseCount) -1

    LET database = Peek('dbname',$(d), 'Databases');

    MyTables:

    LOAD "table_name" as TableNames;

    SQL SELECT table_name FROM information_schema.tables where table_schema= '$(database)';

    LET TableCount = NoOfRows('MyTables');

    FOR t = 0 TO $(TableCount) -1

        LET vMyTableName = '$(database).' & Peek('TableNames', $(t), 'MyTables');

        LET vMyTableNameQVD = Replace(Peek('TableNames', $(t), 'MyTables'), ' ', '');

        $(vMyTableNameQVD):

        SQL SELECT * FROM $(vMyTableName);

        STORE $(vMyTableNameQVD) INTO 'lib://masterqvd/$(database)/$(vMyTableNameQVD).qvd';

        DROP Table $(vMyTableNameQVD);

    NEXT t

NEXT d

Examle DB's structure:
db1 with tables y1,y2,y3
db2 with tables x1, x2, x3

Problem is with somewhere near variable vMyTableName.

While executing line: SQL SELECT * FROM $(vMyTableName); it tries to mix tables from db2 with tables from db1 - eg. SQL SELECT * FROM db2.y1;

Not sure but i think that QS firstly retrieve all tables (from 2 database) instead of doing it in FOR statement.

Any ideas?

1 Solution

Accepted Solutions
Not applicable
Author

I've added DROP Table MyTables; between NEXT t and NEXT d fixed the problem.

Hope that script will be usefull for someone .

View solution in original post

1 Reply
Not applicable
Author

I've added DROP Table MyTables; between NEXT t and NEXT d fixed the problem.

Hope that script will be usefull for someone .