Concatenating 2 tables in 2 different databases

[#1]

Hi All,

I am trying to concatenate two tables (table1 and table4) from 2 different databases in QV script as below (table1 and table4 are joined with different tables within the same database). The script works till the highlighted lines but does give an error when the rest is run all together. Error message is as below:

ERROR MESSAGE

Table not found

INNER JOIN (Table 4)

Table 5:

First 10 LOAD Company,

Field 1,2,3 etc.

Could you please let me know if it is possible to join table4 in the second database first and then concatenate it to table1 created in the first database?

Thanks in advance for all the support.

QV SCRIPT

OLEDB CONNECT TO database

Table 1:
First 10 LOAD

Field 1,2,3 etc    
SQL SELECT *
from database 1

INNER Join (Table 1)
Table 2:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 1

 
INNER JOIN (Table 1)
Table 3:
First 10 LOAD

Field 1,2,3 etc.

FROM database 1

Table 4:
Concatenate First 10 LOAD
Field 1,2,3 etc.   

SQL SELECT *
FROM database 2                                     


INNER JOIN (Table 4)
Table 5:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 6:
First 10 LOAD
Field 1,2,3 etc.  

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 7:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

[vishsaggi]

May be try this?

OLEDB CONNECT TO database

Table 1:
First 10 LOAD

Field 1,2,3 etc   
SQL SELECT *
from database 1

INNER Join (Table 1)
Table 2:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 1

 
INNER JOIN (Table 1)
Table 3:
First 10 LOAD

Field 1,2,3 etc.

FROM database 1

NoConcatenate
Table 4:
First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2                                     


INNER JOIN (Table 4)
Table 5:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 6:
First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 7:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

[maxgro · Accepted solution]

If I understand

- create table 1 and join 2 and 3 with 1

- create (noconcatenate) table 4 and join 5 and 6 and 7  to 4

- add table 4 to table 1

- drop table 4

[Table 1]:
First 10 LOAD

Field 1,2,3 etc    
SQL SELECT *
from database 1

INNER Join ([Table 1])
//Table 2:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 1

 
INNER JOIN ([Table 1])
//Table 3:
First 10 LOAD

Field 1,2,3 etc.

FROM database 1

/*

end of table 1

start table 4

*/

[Table 4]:
NoConcatenate

First 10 LOAD
Field 1,2,3 etc.  

SQL SELECT *
FROM database 2                                     

INNER JOIN [Table 4]
//Table 5:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN [Table 4]
//Table 6:
First 10 LOAD
Field 1,2,3 etc. 

SQL SELECT *
FROM database 2

INNER JOIN [Table 4]
//Table 7:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

Concatenate ([Table 1])

load * resident [Table 4];

drop table [Table 4];

[#4]

Many thanks both of you.

Massimo, that is exactly what I wanted to do and it worked. Thank you very much.

One more request is that if you could please explain the logic of what the last 3 lines you have added do and if dropping table4 means that if I am loosing any fields that belongs to this table or any disadvantages of dropping it?  Also would the same work if I swap table 1 and 4 in the last 3 lines you added?

This will help me understand resident load and drop table functions in my search.

Again many thanks for all the support.

[maxgro]

One more request is that if you could please explain the logic of what the last 3 lines you have added do and if dropping table4 means that if I am loosing any fields that belongs to this table or any disadvantages of dropping it? 

Before the last 3 lines you have 2 tables 1 and 4.

As you want 1 table, use concatenate to add Table 4 to Table 1.

After that you have Table 1 with Table 1 and Table 4 data

and Table 4; so, remove (drop) Table 4

Also would the same work if I swap table 1 and 4 in the last 3 lines you added?

Yes

Concatenate ([Table 1])

load * resident [Table 4];

drop table [Table 4];

[#6]

Many thanks Massimo.