community.borland.com

Article #25158: InterBase to InterBase - Cross database Table-data copy using external tables

Problem:
Cause: DataPump is too slow or crashing.
Problem: Delphi's DataPump expert is too slow and crashes.  
QLI is no longer shipped.

Solution:
	Use WISQL or ISQL or ISQL Script to create and populate 
	external tables and move the data to and from them to avoid 
	the BDE and the problems associated with the DataPump.  
	(example below)

	Restrictions:	
	-  All fields MUST be of fixed length.
	- You MUST create the external file before trying to access it.
	  This is very important when using scripts.
	- The EOL character field is optional (of no real use here).
	- INSERT and SELECT are the only legal operations. This means
	    that (UPDATE and DELETE are illegal.)
	- External file operations are NOT under transaction control.
	- Dropping the database does NOT remove the external file from
    		    your disc.
	- Numeric data should be converted to character using, CAST().
	
	
	--------------------------------------------------------------------------------------------------

	Scenario:	

			Table in Database A (containg data)

  				Table source (f1 CHAR(10),
    					f2 CHAR(20),
    					f3 CHAR(200))

			Table in Database B (empty)

				Table target (f1 CHAR(10),
    					f2 CHAR(20),
    					f3 CHAR(200))

	Example:	
	Connect to Database A

	create table ext_table_source external file "C:ext_filesext_data.txt"
			(f1 CHAR(10),
 			f2 CHAR(20),
 			f3 CHAR(200))

	insert into ext_table_source select f1, f2, f3 from source;
		drop table ext_table_source;
		commit;

	
	Connect to Database B
	create table ext_table_target external file "C:ext_filesext_data.txt"
		(f1 CHAR(10),
		f2 CHAR(20),
		f3 CHAR(200))

	select f1, f2, f3 from ext_table_target;  /* to view records */

	insert into target select f1, f2, f3 from ext_table_target;

	select f1, f2, f3 from target;  /* to verify data transfer */

	drop table ext_table_target;

	commit;

	deleted external file from disc if desired.

Last Modified: 26-OCT-00