10.10.2. Upload a fresh copy of a whole table

<< Click to Display Table of Contents >>

Navigation:  10. FAQ > 10.10. Working with Databases >

10.10.2. Upload a fresh copy of a whole table

 

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 a “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 AN76E5~1_img341 SQLiteWriter Action: See the section 5.24.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)

 

 
We’ll have:

clip0412

 

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).

 

 

AN76E5~1_img5

You can use the AN76E5~1_img344 ODBCReader Action to execute any SQL statement (here above: a “TRUNCATE” statement)

 
 
 

AN76E5~1_img5

See section 5.2.2. about the AN76E5~1_img344 ODBCReader Action.

See section 5.24.3. about the AN76E5~1_img347 Upsert Action.

See section 5.24.4. about the AN76E5~1_img58 CreateTable Action.

See section 5.24.19. about the AN76E5~1_img349 Teradata Writer Action.

 

 

If we are using Teradata, we’ll have:

 

clip0413

To get an even higher “INSERT” speed, instead of using the AN76E5~1_img347 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.