Moving Data Between InterBase Databases Using a Heterogeneous Join.

By: Borland Staff

Abstract: Describes steps using various BDE tools

Problem:
Moving data from a table in one database to a table in another  database may be 
desirable for various reasons:
	- Recovering data in a damaged database
	- Database replication
	- Custom implementation of shadowing


Solution:
InterBase does not perform INSERT's to a table in a database using data taken 
from a table in a different database.  The solution given below is only one of 
several possible solutions that uses clients running the BDE and Delphi, and 
uses the heterogeneous INSERT capability of the BDE.

This solution requires Delphi 2.0 or higher using the Developer, Client/Server or Enterprise SKU, and the BDE with SQL Links for InterBase.  


1. Start the BDE Configuration Utility.

2. On the Aliases page create new aliases or configure exsisting aliases to 
access the desired InterBase database(s) (*.gdb file(s)).

3. When finished save the changes and exit the BDE Configuration Utility.  If 
using TDatabase components in Delphi is desired instead of using BDE aliases do steps 4 and 5.

4. Start Delphi and open up a new or existing application to a Delphi form.  If 
using TDatabase components in place of BDE aliases is desired proceed on to step 
5, otherwise to use BDE aliases to access the InterBase database(s) proceed to 
step 11.

5. Place two database components on the form (it will be assumed that there names are 
     Database1 and Database2).

6. Double-click on the Database1 component to bring up the "Form1.Database1 Database" dialog.

7. In this dialog type in a name in the Name edit control (in this example the Name (actually the DatabaseName) property of this component will be "aaa"), and set the 
Driver name to be INTRBASE.

8. Click on the Defaults button.  This will fill in the Parameter overrides listbox.  
In the listbox set the properties of SERVER NAME, USER NAME, and PASSWORD to appropriate values.  In this example they will be set to:

  SERVER NAME=c:ibserverexamplesemployee.gdb
  USER NAME=SYSDBA
  PASSWORD=masterkey

9. Uncheck the Login prompt to not be presented the Login dialog when then Connected 
property is set to True.  This step is optional.

10. Click on OK.  Repeat steps 5 to 10 for the Database2 component (in this example 
the DatabaseName property of the Database2 component will be "bbb").

11.  Place a TQuery component on the form (in this example the name of the TQuery component will be "Query1").

12. In the Object Inspector, scroll to and click on the SQL property, then click on 
the "..." button to open up the SQL string editor.

13.   Type in the string:

                       'INSERT INTO ":aaa:Country" SELECT * FROM ":bbb:Country" ' 

(without the single quotes, and including the double quotes), and click on OK.  If 
BDE aliases are being used replace the text strings of 'aaa' and 'bbb' with the names 
of the appropriate BDE aliases.
 Also, other syntax possiblities for the INSERT command can be used, though this 
examples is using the syntax shown above.

14. Place a pushbutton on the form.

15. Double-click on the button to set up an Onclick procedure for the button.  If
TDatabase components are used in the form type in the following lines of code:

  Database1.Open;
  Database2.Open;
  Query1.ExecSQL;

otherwise type in the following line of code:

  Query1.ExecSQL;

16. Click on -File | Save- menu choices if desired, to save the changes.

17. Click on the -Run | Run- menu choices to run the application.

18. When the form with the button on it appears, click on the button to perform the INSERT.  If login dialogs appear type in the user name and password, once both 
connections happen the insert will be performed.


WARNING: The following items can affect data that is inserted into the target table:
- Any BEFORE INSERT triggers and AFTER INSERT triggers or stored procedures called by 
the triggers will be activated when data is inserted into the target table.  This can alter data as it is INSERTed into the table (this includes data in key fields).  
Consider performing the command "ALTER TRIGGER  INACTIVE" where appropriate to make a trigger inactive, and prevent changing of the data when it is added into the target table.

- Any indexes that are unique or are primary keys may cause a Delphi  EDBEngineError exception to be generated if any duplicate key field entries are inserted into the 
target table.  The command "ALTER INDEX  INACTIVE"  will make the index inactive and prevent the exception from happening, however this command will likely 
need be done after careful consideration since unique and primary key indexes often 
have a specific purpose.  Also, consider using the TBatchMove component (or the DbiBatchMove() BDE API function call) where any duplicate record is sent to a different table (which will be a local Paradox table).

- Any foreign key references may cause a Delphi EDBEngineError exception to be 
generated if there are not comparable key field entries in the parent table.  Again, consider carefully the option of making the index inactive or using the TBatchMove component (or the DbiBatchMove BDE API function call).

PERFORMANCE: The following item can be done to increase performance:
- To increase performance for bulk INSERTs use the "ALTER INDEX  INACTIVE" 
  command on indexes that are not primary keys and are not unique.  After the INSERT 
is compeleted activate the index and the index will be recomputed.

Server Response from: ETNASC04