Search code examples
sqlexternalteradata

Using lookup list in Teradata (Using Teradata SQL Assistant)

I'm new to Teradata. I have a table with millions of records and I am trying to query based on a subset using a lookup list to feed my where clause.
My lookup list contains thousands of records.

In SQL Assistant this is what I've tried:

SELECT  T.*
FROM a_balance T
JOIN
    OPENROWSET (
             BULK 'c:\myfile.txt',
             FORMATFILE = 'c:\myfileformat.txt'
    ) B ON T.accountID  = B.accountID

What would you recommend to do in Teradata SQL Assistant?

Solution

  • As @denoeth suggested -
    You can create a volatile table.
    (I would still suggest you'll ask for a scratch/playground database)

    VOLATILE
    ...The definition is of a volatile table is retained in memory only for the duration of the session in which it is defined. Space usage is charged to the login user spool space. Because volatile tables are private to the session that creates them, the system does not check the creation, access, modification, and drop privileges. A single session can materialize up to 1,000 volatile tables.

    SQL Data Definition Language Syntax and Examples Release 15.10 B035-1144-151K June 2015

    Import using Teradata SQL Assistant
    (Good for relatively small sets of data)

    "Tools" -> "Options" -> "Import"

    set/unset "Ignore the first record in the import file (Skip Header)"
    Set "Maximum Batch size for simple import" to 999 

    create volatile set table accounts (accountID int) 
unique primary index (accountID) 
on commit preserve rows
;

    "File" -> "Import Data"

    insert into accounts (accountID) values (?);

    "File" -> "Import Data" (cancel selection)