Extract Between Databases

This facility lets you copy records from one database to another — either all the records in the database or just selected records. The records may be copied in their entirety or just certain data items copied to create the new records.

The first choice creates a new database using exactly the same record numbers as the records in the database from which they are copied.Physically the "new” database must already exist, albeit with no records. If there are any existing records in the new database, then first these are all removed.

You might use this option if (for example) you were re-structuring an application master database, and did not want to follow that with an application Audit Pointers option on the transaction database ¾ a necessity if the records in the master database were given new record numbers.

The second option again creates a new database — removing all existing records, if any. The difference over the first option is that records are "compacted” in the new database ¾ that is, they are written consecutively from the beginning, leaving no gaps, even though the original database may have gaps of unused records. If the new database is an application master file, then you will need subsequently to Audit Pointers from the Controls & Audit Manager chapter of the Application Manager.

The third option is used where you want to add the records from one database to those already present in another. Records are added to the end of the second database in sequence, and no account is taken of the record numbers from the first database. In addition, no check is made on whether or not any duplicate records will result.

Note

If you want to merge records from one database into another where duplicates may exist, then you should use other methods. If the files have prime indexes, then use either the data interchange program to export from one database into an ASCII file, and then import it into the other, or use the ProFiler application generator. If the files do not have prime indexes, then you may need to combine the two files, and use the sorted report facilities of Report Generator or ProFiler to help you identify duplicates. In fact ProFiler has special facilities (in user-defined reports) to mark duplicates for removal.

Whatever type of extraction is performed, the steps are very similar. They include:

Specify the database name in the same way as usual (you may use the <F4–Select> function key). The first database specified is the one from which you will copy records (source); the second database is the database to which you will copy (target).

Yes allows you to select the records to copy from the source database. The standard selection criteria screen is offered here. If you reply No, then all the records in the source database are copied.

Automatically Match Data Item Names

The system now matches all data items that have exactly the same names in both the source and target files. The match status screen shown below is then displayed.

This shows the total number of items on each database, the number of those that match by name between the two files, the number of items that do not get extracted because they are calculated in some way (types *, %, F, & and =), and the number of unmatched data items. (Note that extraction into calculated item types is not necessary, because the value of these items is calculated by the program only when accessed. You may copy a calculated field into a number field, however.)

Change or Accept Data Item Matches

You can now edit the data items that are copied from the source database into the target database. Some data items may not have the same name, but you need to match them up. You may not want to copy across all data items, even though they match by name. Here you can either edit all matched items, edit only the unmatched items, or to accept the matching as it now stands. You take this last choice once you have made any changes that you wish to make, and are ready to extract.

If you take this choice, the left side of the screen displays the data item numbers, names and types of 16 data items in the target database starting at the first entry.The right side of the screen displays the items in the source database that have been matched to copy into the target database.

To change or set the source item, press <Enter> or <Down Arrow> to move to the item you wish to amend, and then select the data item by either keying in the name/number of the item or you may use the <F4–Select> as required. If you do not wish to copy a data item that has been matched by the program, delete the data item name using the <Space> bar or <F3–Clear> function.

This choice is similar to the Edit All Data Item Matching option described above, except that only target data items that have not yet been matched are displayed.

As they have not been matched, the source entries are blank.Use the <Enter> key or the upward and downward arrow keys to reach the target item against which you wish to match. Key in the data item name or number and press <Enter> — again the <F4–Select> key may be used to find the source data item you want.

Use this option only when you are happy that the matching is as you require it to be. If you have chosen either of the Copy options above and the target database already has some records in it, you will be asked: existing records on "to" database will be removed. ok to do this?

If you reply No or <Escape> to this, you are returned to the Extract menu. If you reply Y to this, however, you are now asked to commence extract process

You can still <Escape> at this point to abandon the extract and return to the Extract menu. If you key <Enter>, the extraction process now starts. During the extraction the number of the record being processed and the number of the record being written are displayed. At the end, <Enter> returns you to the Extract menu.

If the target database has a prime or secondary index you are reminded that you must audit the database following this routine.

Article ID: 1250
Created On: Fri, Jun 29, 2012 at 11:26 AM
Last Updated On: Tue, Jun 20, 2023 at 5:17 PM

Online URL: https://kb.datafile.co.uk/article/extract-between-databases-1250.html