topic Re: How to load all tables from sql DB? in QlikView

How to load all tables from sql DB?

Tue, 20 Nov 2012 10:36:55 GMT

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;

Re: How to load all tables from sql DB?

jagan — Tue, 20 Nov 2012 13:11:43 GMT

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.

Re: How to load all tables from sql DB?

Wed, 21 Nov 2012 07:08:31 GMT

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?

Re: How to load all tables from sql DB?

jagan — Wed, 21 Nov 2012 10:08:37 GMT

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.

Re: How to load all tables from sql DB?

flipside — Wed, 21 Nov 2012 10:32:41 GMT

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.

Re: How to load all tables from sql DB?

Thu, 22 Nov 2012 13:27:34 GMT

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

ODBC CONNECT TO {connection};

SQLTableColumns:

SQLCOLUMS in KQ0....

Re: How to load all tables from sql DB?

flipside — Thu, 22 Nov 2012 13:40:26 GMT

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