You'll need to import all at once, but whether you need to import into a "staging table" and then move the data from there is not a question I can answer from the info provided.
Set up two relationships, one that match records by ID and a second that matches records by name. This requires two occurrences of one of your tables to get the two relationships. Use the name matching relationship with Replace Field Contents to copy over the matching ID. This only works if: names are unique and your names match perfectly (no Phil instead of Phillip, no typos...)
Made a sample db to try to understand the Further Question part... Doesn't work the way I have it set up. Perhaps you can see my wrong?
Table 1 would need an artist name field and such is missing from your sample file. That then allows you to match Table1::artist_Name to Table 2 2::artist_Name.
Then, on a layout based on Table 2 2, you can use Replace Field Contents to copy over Table1::__pk_Table1ID to Table 2 2::__kp_ArtistID.
Don't understand the last part. Table1::__pk_Table1ID to Table 2 2::__kp_ArtistID. Never really intended to use Table1::__pk_Table1ID for anything.... I somehow need to translate Artist_Name in Table 2 to give Table1 the primary key for field Table1: _kf_ArtistID... are you doing something special in "Replace Field Contents"?
Sorry, I misread your example relationships. And a second look leaves me confused.
Which table represents the table of newly imported data that contains an artist name but to which you want to link by Artist ID?
Which represents your existing table of artist_ID values and Artist Names.
Table 1 doesn't seem to be either of those tables as it doesn't have the expected Artist_Name field that should be in both tables.
Table1 will hold the imported data. A popup menu will holds the _kf_ArtistID and displays all the names from Table2. This is set up so pop up box selects from a value list using values from Table2 but only shows values from second field Artist_Name. See attached on how pop up box for Table1:_kf_ArtistID is set up.
Then table 1 is missing the artist name field.
quoting from your original post:
If the imported record name field is "John"
Since this is only to get the data into the new db I resolved this by
Putting the imported name into a variable.
Perform find in Table2 for the distinct name.
Putting the __kp_ArtistID from the found name in a variable.
Set the field _kf_ArtistID to the ArtistID variable and name shows in popup box.