Import from one table to another

[FLabrecque]

I wish to use one of my database (call it DB #A) to import ODBC tables into a second database (DB #B). From what I figure, I can't directly import into another DB. I'll have to use these steps:

1. Delete Link in #A that points to #B.Table.
2. Import table in #A.
3. Delete Table in #B.
4. Export table in #A to #B.
5. Delete Table in #A.
6. Create a link in #A that points in #B.

Is there any simpler ways to do this? I would really enjoy discovering a way to directly import in #B. Otherwise, #A gets a lot of bloat (especially since I need to import on a daily basis 6 tables of 60,000-100,000 records)

 

[llkhoutx]

Join your tables appropriate in a query, then delete the records returned.

You'll have multiple such queries.

 

[Pat Hartman]

You can use the "IN" clause in SQL. I've copied the entire help entry because SQL help is difficult to come by. You need to know the trick. Notice that it tells you that linked tables are more efficient than using "IN".

IN Clause 
Identifies tables in any external database to which the Microsoft Jet database engine can connect, such as a dBASE or Paradox database or an external Microsoft® Jet database.

Syntax
To identify a destination table:

[SELECT | INSERT] INTO destination IN
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

To identify a source table:

FROM tableexpression IN 
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

A SELECT statement containing an IN clause has these parts:

Part Description 
destination The name of the external table into which data is inserted. 
tableexpression The name of the table or tables from which data is retrieved. This argument can be a single table name, a saved query, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN. 
path The full path for the directory or file containing table. 
type The name of the database type used to create table if a database is not a Microsoft Jet database (for example, dBASE III, dBASE IV, Paradox 3.x, or Paradox 4.x). 


Remarks
You can use IN to connect to only one external database at a time.

In some cases, the path argument refers to the directory containing the database files. For example, when working with dBASE, Microsoft FoxPro®, or Paradox database tables, the path argument specifies the directory containing .dbf or .db files. The table file name is derived from the destination or tableexpression argument.

To specify a non-Microsoft Jet database, append a semicolon (;) to the name, and enclose it in single (' ') or double (" ") quotation marks. For example, either 'dBASE IV;' or "dBASE IV;" is acceptable.

You can also use the DATABASE reserved word to specify the external database. For example, the following lines specify the same table:

... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];

... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"



--------------------------------------------------------------------------------

Notes

For improved performance and ease of use, use a linked table instead of IN.

You can also use the IN reserved word as a comparison operator in an expression. For more information, see the In operator.

 

[FLabrecque]

You suggest that I have a dummy table in DB #B, and use an insert to populate it, with an SELECT statement to my ODBC table.

This would be fine if my table was from Oracle, MySQL or other more "standard" sources. It comes from a mainframe. The way it is set, I can't link to it's data (or risk loosing the link in the process). It would be fun to ask the mainframe guys to change it, but that's not an option.

Other suggestions?

By the way, I do appreciate your help!

EDIT: I should state that this argument doesn't come from me, but from the mainframe usage policy, saying "can't link tables".

 

[cable]

then its not really a table link problem is it?? its more of a export/import excerise, in which case its just a case of getting the mainframe data out (connectdirect or whatever) and then importing it into the db.

 

[FLabrecque]

It never was a link problem. The only link I talked about, was between DB #A & DB #B. Perhaps I wasn't clear. The imported table comes from Source #C.

 

[Pat Hartman]

You suggest that I have a dummy table in DB #B, and use an insert to populate it, with an SELECT statement to my ODBC table.

- It's not a dummy table. You seem to want the data to end up in DB #B.

Why do you think you'll have trouble linking to a db on the mainframe? It is just the big server in the sky. I have used DB2 backends to many Access databases. In fact, I think they are more stable and easier to work with than SQL Server.

If you want to get the data out of another db, at some point you're going to have to "link" to the db. You can do it efficiently with a linked table or inefficiently by linking at runtime.

If you are not linking to the mainframe tables to import the data in step 2, how are you getting the data to import? Did you run a SPUFI or something to export it first?

 

[FLabrecque]

First, it's company policy to not link on the mainframe. It's not my decision, and I'm not here to defend that (as pointed before). And no, I can't get to change their minds.

My initial question was if there was an easier way to do this, which meant importing directly into another DB (or in this case, several other DBs).

I've coded my initial process. Which works, but I have a lot of bloat created that I'll need to purge often. Thank you for your response.

 

[Pat Hartman]

You didn't answer the question of how you are getting the data now. Import is not descriptive. There are ways of running batch jobs on the mainframe that have as a final step an FTP to a server so you can pick up the text file from there.

Creating and deleting sets of data causes database bloat. There is no getting around it. One thing you can do is to isolate the tables you are importing from the mainframe into a separate .mdb. Each time you need to recreate the tables, start with an empty db. You can keep a compacted version of the .mdb with all the tables but no data in a hidden directory. As the first step in the import process, delete the old .mdb and copy the empty one to replace it. This will eliminate the bloat problem for this set of tables. Since the db name remains the same, you won't have any trouble with links to the .mdb.