I am new to Teradata SQL; I have a database containing 10 million records(rows) of revenue, each with a unique ID. I need to query 3 million records out of it, in which I do have the IDs of these 3 million records. the direct and slowest way would be:
select ID, revenue
where ID in ('00000001', '000000002', '00000004'.................)
so I have to copy & paste 3 million records into the WHERE clause to get this, very time-consuming. Any better alternative way I can query the 3 million records out of 10 million?
thanks a bunch guys!!
the 3 million records is stored in a local txt file; so I guess to rephrase my question: how do I upload the 3 million records to Teradata SQL Assistant as a temp table? so in that way I can join the temp table with my 10 million table.
anyone know the syntax how to upload txt file as temp table in Teradata?
loading via sql assistant is going to be painfully slow. you need to put together a TPT script to load the file into a permanent table on your TD system, or use the TDLoad option for TPT, quick and painless, much faster than an OLEDB load via sql assistant which will run one insert per row...
Slow yes, but here's how in case you don't have access to TPT or fastload.
SQLA, choose tools menu/options/(import/export)>> choose delimiter(tab for instance)
then Save your file as tab delimited.
Choose import from File menu.
SQL >> insert into yourtemptable values(?,?,?...); - one ? for each field.
Submit request, you will be prompted to choose file.
note: this can be found in help menu also.
Also make shure you set the "Maximum batch size for simple imports" in Tools-Options-Import to 999 (seems to be the max).
Additionally using .NET instead of ODBC should be faster.