在Teradata中使用查找列表（使用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.

我是Teradata的新手。我有一个包含数百万条记录的表，我正在尝试使用查找列表基于子集进行查询以提供我的where子句。我的查找列表包含数千条记录。

In SQL Assistant this is what I've tried:

在SQL Assistant中，这是我尝试过的：

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?

你会建议在Teradata SQL Assistant中做什么？

1 个解决方案

#1

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

正如@denoeth建议的那样 - 您可以创建一个易失性表。（我仍然会建议你要一个临时/游乐场数据库）

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.

VOLATILE ...定义是一个volatile表，只在定义它的会话期间保留在内存中。空间使用量由登录用户假脱机空间计费。由于volatile表是创建它们的会话的私有表，因此系统不会检查创建，访问，修改和删除权限。单个会话可以实现多达1,000个易失性表。

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

SQL数据定义语言语法和示例版本15.10 B035-1144-151K 2015年6月

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

使用Teradata SQL Assistant导入（适用于相对较小的数据集）

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

“工具” - >“选项” - >“导入”

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

set / unset“忽略导入文件中的第一条记录（Skip Header）”将“简单导入的最大批量大小”设置为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)

“文件” - >“导入数据”（取消选择）

#1