Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have multiple table in a database:
TEST_2015_01
TEST_2015_02
TEST_2015_03
TEST_2015_04
TEST_2015_05
TEST_2015_06
TEST_2015_07
I need to load all of them in a single table.
I am trying the below but it does not seem to work:
SQL SELECT *
FROM "ABCD_BKP".dbo.TEST_*;
Next
Any ideas?
Kind Regards,
Hasvine
I have generated Table Name from Jan 2015 to Current month
Table:
LOAD 'TEST_' & date(Monthstart(Date),'YYYY_MM') as Name;
LOAD makedate(2015,1,1)+iterno()-1 as Date
Autogenerate 1
While makedate(2015,1,1)+iterno()-1 <= today();
Final:
LOAD * inline [
Junk ];
for i=1 to fieldvaluecount('Name')
let vName = fieldvalue('Name',$(i));
concatenate(Final)
LOAD *,
'$(vName)' as TableName;
SQL SELECT *
FROM "ABCD_BKP".dbo.$(vName)";
NEXT i
drop field Junk;
Store Final into ..\Final.QVD;
You can use the SQLTABLES SQL function to retrieve a list of your tables in your DB. Then iterate over the tables names using FOR ... NEXT
Have a look at your DBMS manual, there are also examples here in the forum how to do this in QV script.
Tables:
LOAD * inline [
Name
TEST_2015_01
TEST_2015_02
TEST_2015_03
TEST_2015_04
TEST_2015_05
TEST_2015_06
TEST_2015_07 ];
Final:
LOAD * inline [
Junk ];
for i=1 to fieldvaluecount('Name')
let vName = fieldvalue('Name',$(i));
concatenate(Final)
LOAD * ;
SQL SELECT *
FROM "ABCD_BKP".dbo.$(vName)";
NEXT i
drop field Junk;
Store Final into ..\Final.QVD;
Hi Kushal,
I used to do that but the problem is that every month a new table is created in the database.
We have already reached like table TEST_2016_04
I need a dynamic way to do it.
Many Thanks,
Hasvine
I have generated Table Name from Jan 2015 to Current month
Table:
LOAD 'TEST_' & date(Monthstart(Date),'YYYY_MM') as Name;
LOAD makedate(2015,1,1)+iterno()-1 as Date
Autogenerate 1
While makedate(2015,1,1)+iterno()-1 <= today();
Final:
LOAD * inline [
Junk ];
for i=1 to fieldvaluecount('Name')
let vName = fieldvalue('Name',$(i));
concatenate(Final)
LOAD *,
'$(vName)' as TableName;
SQL SELECT *
FROM "ABCD_BKP".dbo.$(vName)";
NEXT i
drop field Junk;
Store Final into ..\Final.QVD;