It’s very common to need to upload inside the database an “updated” copy of some result table.
The first question is the following: Are you using a database as an “inter-operability” platform to quickly exchange data with a CRM/Visualization tool that has its own storage system? If that’s the case, I’d suggest you to use a SQLite database. Indeed, the “INSERT” speed (which is usually the only bottleneck when working with a database) inside SQLite databases is from 10 to 1000 times higher than inside a “normal” database. To create/update a SQLite database, use the SQLiteWriter Action: See the section 5.25.6 (and 5.2.4.) for more details.
The typical steps to “upload a fresh copy of a table” are:
1.Optionally: Create the table using a “CREATE TABLE” statement.
2.Truncate the table (to remove any “old” rows).
3.(If required: Drop all indexes)
4.Insert the new rows inside the table.
5.(If required: Re-create the indexes)
In such very simple scenario, you should:
•…avoid using any “UPDATE” statements (because databases are usually extremely slow when processing “UPDATE” statements): i.e. it’s faster to use “TRUNCATE” and then “INSERT”.
•…avoid any INDEX on your table (because databases are much slower when inserting rows inside an INDEXed table).
If we are using Teradata, we’ll have:
To get an even higher “INSERT” speed, instead of using the Upsert Action, you might be tempted to use a “bulk upload” tool. Indeed, many database vendors offer specialized tools called "bulk upload" that allow you to copy a text file into the database at a very high speed: See the next section for some comments about these "bulk upload" tools.