All Forums Database
LeoDong 2 posts Joined 01/15
30 Jan 2015
how to query large rows in Teradata SQL?

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
from My_Table
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!!

LeoDong 2 posts Joined 01/15
30 Jan 2015

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?

VandeBergB 182 posts Joined 09/06
30 Jan 2015

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

Some drink from the fountain of knowledge, others just gargle.

Glass 225 posts Joined 04/10
31 Jan 2015

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.

dnoeth 4628 posts Joined 11/04
31 Jan 2015

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.


You must sign in to leave a comment.