Teradata Passthrough Syntax Error Troubleshoot

[IgawaKei29]

I have been looking at this code syntax for hours, the Teradata code runs in a different environment, but bringing it through Enterprise Guide is giving me issues.  The error I am getting is: ERROR: Teradata execute: Syntax error: expected something between ')' and ';'.  I can't seem to figure out what the issue is.  I appreciate any help or insight on this.

PROC SQL NOERRORSTOP;
CONNECT TO TERADATA (AUTHDOMAIN=xx  server="xxx" connection=global mode=teradata);
  execute (drop table TestServer.Test3) BY Teradata;
  execute (create table TestServer.Test3 as
  
(
select *
from TestServer.Test1

UNION ALL

select * 
from TestServer.Test2

))BY Teradata;
 quit;

[SASKiwi]

Try it without the extra brackets:

PROC SQL NOERRORSTOP;
CONNECT TO TERADATA (AUTHDOMAIN=xx  server="xxx" connection=global mode=teradata);
  execute (drop table TestServer.Test3) BY Teradata;
  execute (create table TestServer.Test3 as
select *
from TestServer.Test1
UNION ALL
select * 
from TestServer.Test2
) BY Teradata;
 quit;

[IgawaKei29]

I tried without the extra brackets and got the message:
ERROR: Teradata execute: Syntax error, expected something like a name or a Unicode delimited identifier or '(' between the 'as'
keyword and the 'select' keyword.

[Tom · Accepted solution]

In Teradata syntax there are two key things you have left out.

1) At the end of the CREATE TABLE statement you need some like WITH DATA (look up the syntax I don't remember it exactly) if you actually want the data to be saved.

2) Teradata is a highly parallel setup.  It does this by partitioning the data with a PRIMARY INDEX.  You want a primary index that does not skew the data so that only some of the "nodes" have all of the data.  If you do not specify the PRIMARY INDEX then it will pick the first variable.  Which might be a very poor choice.  

[IgawaKei29]

Thank you! That was it.. I was missing the With Data and Primary Index! Really appreciate the help.

) with data primary index(id)) by teradata;