Archives of the TeradataForum
Message Posted: Thu, 22 Jun 2006 @ 18:17:05 GMT
| Subj: | | Re: Using Insert SQL of Teradata v.s. Informatica Maps |
| |
| From: | | John Graas |
TeradataForum wrote:
| | 1. From staging to Target, can we use Insert SQL feature of Teradata instead of ETL Tool Informatica? | |
| | 2. How do we use Insert SQL feature for performing a lookup? | |
| | 3. I know for sure that lookups, etc can be performed using joins in Insert SQL of Teradata, but what are the pros and cons of using Insert
SQL instead of Informatica mapping | |
My initial reaction is that you might try an ELT design:
a) load the insert rows into a staging table on Teradata using a load utility (very fast)
b) insert into the target table using a select from the staging table with a join against the lookup table -- one step for all
rows.
If you try and Insert/Select one row at a time from the client, then you could be getting table level locks for each row to be inserted:
a performance hit.
Another alternative is to use Stored Procedures, with the SP first performing a single row lookup followed by a single row insert for each
transaction.
jdg
www.jgraas.com
| 
