Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to load all tables from sql DB?

Hi

How can I make QV to load all tables from a SQL DB?

Something like

Load *;

SQL SELECT *

FROM KQ0.*

Somekind of loop function?

Edit:

I found and tryd this but did not work. All I get is the TABLE_NAME table.

 

Sub LoadTableNames

SQLTableList:

LOAD "TABLE_NAME" as  TableNames;

SQL SELECT "TABLE_NAME"FROM KQ0."INFORMATION_SCHEMA".COLUMNS;

End Sub;

Call LoadTableNames;

Let vTableCount = NoOfRows(‘SQLTableList’);

Sub LoadTableData

For i = 0 To $(vTableCount) -1

LET vMyTableName = Peek(‘TableNames’, $(i), ‘SQLTableList’);

$(vMyTableName):

SQL SELECT *FROM KQ0.KQ0$(vMyTableName);

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

DROP Table $(vMyTableName);

Next i

End Sub;

Call LoadTableData;

1 Solution

Accepted Solutions
flipside
Partner - Specialist II
Partner - Specialist II

Good timing, I've been doing something very similar today, this is my code.  You can't filter the data returned by SQLCOLUMNS or SQLTYPES directly.  You have to run the code then move the data to another table and filter at that point ...

ODBC CONNECT TO {connection};

AllODBCTables:

Load *;

SQLCOLUMNS;

ODBCDataTypes:

Load *;

SQLTYPES;

DISCONNECT;

ODBCTypes:

Load

    DATA_TYPE as dtDataType,

    concat(TYPE_NAME,',') as dtTypeNameList //I have multiple options so am combining!!

resident ODBCDataTypes

group by DATA_TYPE;

ODBCTables:

LOAD

    //TABLE_QUALIFIER as tblQualifier, //no data

    TABLE_OWNER as tblOwner,

    TABLE_NAME as tblName,

    COLUMN_NAME as tblColName,

    DATA_TYPE as tblColDataType,

    //TYPE_NAME as tblColTypeName, //no data

    PRECISION as tblColPrecision,

    LENGTH as tblColLength,

    SCALE as tblColScale,

    RADIX as tblColRadix,

    NULLABLE as tblColNullble,

    //REMARKS as tblColRmrks, //no data

    //EXTENDED_ID as tblColExtID, //no data

    DATA_SOURCE as tblDSource

resident AllODBCTables where etc, etc;

left join (ODBCTables)

LOAD

    dtDataType as tblColDataType,

    dtTypeNameList

resident ODBCTypes;

DROP TABLES AllODBCTables, ODBCDataTypes, ODBCTypes;

This isn't loading any data, just the table structures.  Hope this helps ...

flipside

View solution in original post

6 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

This is not good practice to load all tables in a loop, because you don't actually require all the tables for report, also all columns in the tables are not required. 

Also in For loop the execution will be very slow.  In my view writing and loading the data manually from each table is best option. 

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

Hi

Maybe I should told why I would like to do this. I´m searching for the table/s that holds the plain text. Most of the other fields in DB are just numbers. When extracting the "Table_Name" table and searching after the fields I´m looking for I can´t find them.

I understand that the operation will be slow and not best practice but the db aint so big so I beliwe it would work.

So back to my orginal question, anyone sees why the above loop don´t work?

jagan
Luminary Alumni
Luminary Alumni

HI,

Try this script.

SQLTableList:

LOAD "TABLE_NAME" as  TableNames;

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

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

Regards,

jagan.

flipside
Partner - Specialist II
Partner - Specialist II

Hi,

If you try this code, it will bring back the DataType of each column it finds in the connection ...

ODBC CONNECT TO {odbc name};

SQLCOLUMNS;

Is this what you are looking for?

flipside

EDIT:  Just add a preceding load to create a table you can then use in some dynamic script.

ODBC CONNECT TO {connection}; //or oledb

SQLTableColumns:

load *;

SQLCOLUMNS;

You CAN also use a named table without the preceding load ...

SQLTableColumns: SQLCOLUMNS;

... Script Editor shows this as an error but it does run.

Not applicable
Author

Thanks. How can I do to load all tables in KQ0. **** ?

ODBC CONNECT TO {connection};

SQLTableColumns:

SQLCOLUMS in KQ0....

flipside
Partner - Specialist II
Partner - Specialist II

Good timing, I've been doing something very similar today, this is my code.  You can't filter the data returned by SQLCOLUMNS or SQLTYPES directly.  You have to run the code then move the data to another table and filter at that point ...

ODBC CONNECT TO {connection};

AllODBCTables:

Load *;

SQLCOLUMNS;

ODBCDataTypes:

Load *;

SQLTYPES;

DISCONNECT;

ODBCTypes:

Load

    DATA_TYPE as dtDataType,

    concat(TYPE_NAME,',') as dtTypeNameList //I have multiple options so am combining!!

resident ODBCDataTypes

group by DATA_TYPE;

ODBCTables:

LOAD

    //TABLE_QUALIFIER as tblQualifier, //no data

    TABLE_OWNER as tblOwner,

    TABLE_NAME as tblName,

    COLUMN_NAME as tblColName,

    DATA_TYPE as tblColDataType,

    //TYPE_NAME as tblColTypeName, //no data

    PRECISION as tblColPrecision,

    LENGTH as tblColLength,

    SCALE as tblColScale,

    RADIX as tblColRadix,

    NULLABLE as tblColNullble,

    //REMARKS as tblColRmrks, //no data

    //EXTENDED_ID as tblColExtID, //no data

    DATA_SOURCE as tblDSource

resident AllODBCTables where etc, etc;

left join (ODBCTables)

LOAD

    dtDataType as tblColDataType,

    dtTypeNameList

resident ODBCTypes;

DROP TABLES AllODBCTables, ODBCDataTypes, ODBCTypes;

This isn't loading any data, just the table structures.  Hope this helps ...

flipside