Create table in Teradata

KNIME Analytics Platform
,
#1

Hi,

I’m currently trying to use Databases nodes only to process data and create a new table containing the resulting data (i’m working on Teradata).
My problem is that, once I joined all the tables I needed, I’m not able to create the resulting table back in the database using the Database Connection Table Writer. The error message, which I don’t really understand, is:
“Execute failed: [Teradata Database] [TeraJDBC 16.20.00.02] [Error 3706] [SQLState 42000] Syntax error: expected something between ‘)’ and ‘;’”

I tried to use a Database Query node with the following code:
“create table my_table as (
select * from #table# temp_table)
with data”
and got the following error message:
“[Error 3707] [SQLState 42000] Syntax error, expected something like a name or a Unicode delimited identifier or an ‘UDFCALLNAME’ keyword or a ‘SELECT’ keyword or ‘(’ between ‘(’ and the ‘create’ keyword.”

I would also like to avoid going through a Database Connection Table Reader + Database Writer since my table is quite heavy (500K rows x 70 columns) and the process looks very slow.

Did someone already face this issue and have an idea of how to tackle it?

Thanks in advance for any help!
Arie

#2

Hi Arie,
for DDL statement you need to use the Database SQL Executor. In your case you could extract the result of the Joiner e.g. the query that defines the data you want to persist in a new db table with the SQL Extract node. See the attached workflow for an example on how to extract the SQL and manipulate it before executing it with the Database SQL Executor node.
Bye
Tobias

teradataCreateTableAsSelect.knwf (21.7 KB)