Automating Drop, Create, and Import data In Toad from access

Toad for Oracle
1

Is there a way to automate toad to drop then create tables and then have it import specific tables from access to the ones it just created in toad?

2

There is an option in the data import wizard to truncate the destination table before import, but not drop and recreate.

3

Okay thanks. Currently I drop tables then create them and import the matching tables from access into toad.

I was looking for toad to automate this because some tables take 10 minutes or more to import. Its just becoming a time consuming process as to where I cant do anything while its importing.

4

Are you checking the “Use Array DML” checkbox on the last step of the import wizard? That will make things go much faster.

5

I am not certain what that does. but if it truncates the data I can’t have that. All of the data is used for reporting purposes and truncating the data makes it unusable for my given situation.

6

I suggested truncate so you could start with an empty table before importing without having to drop and create. If you are dropping and creating the table, you are effectively truncating.

“Use Array DML” has nothing to do with truncating though. That will cause each insert statement to insert a batch of rows, rather than one at a time. It really speeds things along. I would guess that your 10 minute import would drop down to 30 seconds or less.

7

Thank you so much. I will give it a shot and see how much quicker it is. based on your prior statement though i dont understand how truncate is the same as drop and creating a table? Thanks again!

8

Well, it’s the same in the sense that after drop and create, the table is empty, same as if you truncate instead. If you are creating the table so that it is different in structure, then that’s a different story.

9

Okay i see. One thing that i do is that some of the tables in access have a check box so in toad I can’t just import the table instead I select the query to update the table in toad. Will using the truncate command cause an issue with this?

Also, if I use the truncate command can I automate the process of importing the tables now?

Thanks again for your help.

10

I’m not sure what you mean exactly by that checkbox, but you should be able to automate the process if you are importing the same table (or tables) from Access to Toad each time.

Go to Automation Designer in Toad.

Create an “Import Table data” action, double-click it to set it up, then Apply.

You can set up as many as you need.

Then to run it, you can run the App (the “folder” on the left) that contains them all, or you can run each action separately. You can run it from command line or windows scheduler.

There should be plenty of help/videos about Toad’s automation designer, but if you get stuck, feel free to ask here.