For forums, blogs and more please visit our
Developer Tools Community.
By: Borland Staff
Abstract: Describes steps using various BDE tools
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
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
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:
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:
otherwise type in the following line of code:
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.
Could not retrieve comments. Please try again later.
Free Developer Edition!
Click here to download a free non-expiring Developer Edition or 30-day trial >
More InterBase Info
InterBase XE7 Product Info
Free Developer Edition download
InterBase on Google+
Follow @InterBase on Twitter
Server Response from: ETNASC04