Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a database with many tables. I started to load the list of sqltables and filtered them.
Now I want to load in the script many tables at once without scriptcode for every single table.
After Loading I want to store them in a Directory as csv.
For that I want to create a variable, that gives me back the Table_SCHEMA-entry (CODA)
Additionally I wanted to create a second variable (varTableName), that give me the TableName-entry (efaEFA_ADOCTMP, EFA_ASSET and so on)
I tried this to load automatically:
LET DirDataQVD = 'F:\CODA\';
Tmp_sqltables:
Load *;
SQLTABLES;
sqltables:
Load
TABLE_SCHEMA,
TABLE_TYPE,
TABLE_NAME
resident Tmp_sqltables
where TABLE_SCHEMA = 'CODA' AND TABLE_TYPE = 'TABLE' AND TABLE_NAME like 'EFA*' ;
drop table Tmp_sqltables;
Let vTableName = TABLE_NAME;
Let vTableScheme = TABLE_SCHEMA;
// ------------------------------------------------------------------------------------------
$(vTableName):
SQL SELECT *
FROM $(varTableName).$(varTableName);
store $(vTableScheme) into $(DirDataQVD)$(vTableName).csv (txt, delimiter is ';');
drop Table $(vTableName);
But unfortunately it is not working
(TABLE_NAME.TABLE_NAME is wrong, correct would be FROM CODA.EFA_ASSET)
How can I realise this? (please see attached my file)
very much thanks in advance.
Chris
You need to use a loop to iterate through the Temp_sqltables table and for each row use the peek function to retrieve the field values and store them in the variables.
You can find an example here that does the same with excel sheets, which are also tables retrieved with the SQLTABLES command: Re: Loading Multiple Excel Files and Multiple Excel sheets
I think you will need to use Peek() function to store the value of your table_name and table_schema into a variable. You cannot just do LET vVar = table_name
Hi you both,
that sounds good. Would you please help me with that?
I do not get it working.
Thanks.
Chris
Hi Christian,
First of all looking into your script and error message you have a typo.
You need to put varTableSchema instead of varTableName in below statement.
$(vTableName):
SQL SELECT *
FROM $(varTableName).$(varTableName);
Fix it and try other suggestions with loop and Peek() function.
Hope this helps.
Andrei
ODBC CONNECT...........
LET DirDataQVD = '.'; // use your folder
Tmp_sqltables:
Load *;
SQLTABLES;
sqltables:
Load
TABLE_SCHEM,
TABLE_TYPE,
TABLE_NAME,
TABLE_CAT
resident Tmp_sqltables
where TABLE_CAT = 'sisim_stat' AND TABLE_TYPE = 'TABLE' AND TABLE_NAME like 'd*'
; // replace with your schema, table_name
drop table Tmp_sqltables;
for i=0 to NoOfRows('sqltables')-1
Let vTableName = peek('TABLE_NAME', $(i), 'sqltables');
Let vTableCat = peek('TABLE_CAT', $(i), 'sqltables');
Let vTableScheme = peek('TABLE_SCHEM', $(i), 'sqltables');
trace vTableName=$(vTableName); // for test
trace vTableCat=$(vTableCat);
trace vTableScheme=$(vTableScheme);
$(vTableName):
SQL SELECT top 10 * // remove top 10
FROM $(vTableCat).$(vTableScheme).$(vTableName);
store $(vTableName) into [$(DirDataQVD)\$(vTableName).csv] (txt, delimiter is ';');
drop Table $(vTableName);
NEXT