how to query large rows in Teradata SQL?

Database

how to query large rows in Teradata SQL?

Hi

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

Tags (2)
4 REPLIES

Re: how to query large rows in Teradata SQL?

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?

thanks!!

Re: how to query large rows in Teradata SQL?

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

Tallyho!

N/A

Re: how to query large rows in Teradata SQL?

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.

RGlass

N/A

Re: how to query large rows in Teradata SQL?

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.