A tab in the Query Builder is related to a specific database. Although the Query Builder supports the USE database command to switch to another database, it is not possible to have two connection at the same time. This makes it impossible to copy table data from one database to another.
To allow plugin users to exchange data between different databases two plugin commands were added. Please note these are NOT MySQL commands. They only work within the Query Builder.
WPDAVAR (or wpdavar)
WPDAVAR saves the last query result in a variable. The following code selects all records from table genre and stores the result in WP Data Access variable my_genres:
select * from genre
/
wpdavar my_genres
/WPDATMP (or wpdatmp)
WPDATMP creates a temporary table from the data available in a variable that was previously saved with WPDAVAR. The following code extends the previous code by creating a temporary table from the query result available in variable my_genres. The temporary table get the same name as the original table.
select * from genre
/
wpdavar my_genres
/
use rdb:my_remote_database
/
wpdatmp my_genres
/Exchanging data between different databases
The following example shows how table genre is copied from my remote database to my wordpress database.
use rdb:my_remote_database
/
select * from genre
/
wpdavar my_genres
/
use wordpress
/
wpdatmp my_genres
/
select * from genre
/
insert into genre select * from genre
/
Limitations
The previous example copies table genre entirely into variable my_genre. This might require a lot of memory and will not work with large tables. For large tables the procedure needs to be divided in a number of smaller chunks.