Syntax error: INTO clause is not allowed.

Database
Enthusiast

Syntax error: INTO clause is not allowed.

Hello. I wrote the following code in Teradata Studio:

 

SELECT *

INTO TEMP

FROM TEST

WHERE GROUPER=1;

 

However, I kept getting the following error message:

 

Executed as Single statement.  Failed [3706 : 42000] Syntax error: INTO clause is not allowed.
Elapsed time = 00:00:00.003
 
STATEMENT 1: Select Statement failed.

 

Any way to resolve this issue.


Accepted Solutions
Enthusiast

Re: Syntax error: INTO clause is not allowed.

Sorry, I probably misread a little the first time. You can also do a CREATE TABLE with select as shown below. It's not as simple and neat as SQL Server, but this is Teradata and it's what you've got to work with.

 

CREATE [SET/MULTISET] TABLE COLTEST1 AS

(

SELECT *

FROM TEMP

WHERE GROUPER='1'

) WITH DATA

PRIMARY INDEX(your_col_index);

 

But my previous answer remains the same - repetitive tasks like this where only one or two variables change are going to be better suited for a macro or a procedure.

1 ACCEPTED SOLUTION
6 REPLIES
Enthusiast

Re: Syntax error: INTO clause is not allowed.

Correct syntax would be:

 

INSERT INTO TEMP
SELECT *

FROM TEST

WHERE GROUPER=1;

 

TEMP must already exist/already been created by a CREATE TABLE table statement for this to work.

Enthusiast

Re: Syntax error: INTO clause is not allowed.

So, what would be the syntax if I'm subsetting data based on groupers (note the syntax is from SQL SERVER):

 

SELECT * INTO COLTEST1 FROM TEMP WHERE GROUPER='1';

SELECT * INTO COLTEST2 FROM TEMP WHERE GROUPER='2';

SELECT * INTO COLTEST3 FROM TEMP WHERE GROUPER='3';

SELECT * INTO COLTEST4 FROM TEMP WHERE GROUPER='4';

SELECT * INTO COLTEST5 FROM TEMP WHERE GROUPER='5';

 

It seemed creating five tables and inserting the data in with 'insert into' would take forever.

Enthusiast

Re: Syntax error: INTO clause is not allowed.

INSERT INTO COLTEST1

SELECT * FROM TEMP

WHERE GROUPER='1';

 

INSERT INTO COLTEST2

SELECT * FROM TEMP

WHERE GROUPER='2';

 

And so on through your 5th iteration below. A better way would be to create a macro or a procedure (see CREATE PROCEDURE documentation) to complete the redundant steps. You could accomplish with either a loop or just passing a parameter and execute the macro/call the procedure 5 times for your variables 1-5.

Enthusiast

Re: Syntax error: INTO clause is not allowed.

Sorry, I probably misread a little the first time. You can also do a CREATE TABLE with select as shown below. It's not as simple and neat as SQL Server, but this is Teradata and it's what you've got to work with.

 

CREATE [SET/MULTISET] TABLE COLTEST1 AS

(

SELECT *

FROM TEMP

WHERE GROUPER='1'

) WITH DATA

PRIMARY INDEX(your_col_index);

 

But my previous answer remains the same - repetitive tasks like this where only one or two variables change are going to be better suited for a macro or a procedure.

Enthusiast

Re: Syntax error: INTO clause is not allowed.

Okay. But I'm curious whycode is not pull those row with grouper=1. Grouper is a small int and I'm not sure if that going effect how it pull the data, but for some reason it is not pull in the data for those row with grouper='1'.

 

EDIT: Nevermind, I see that it did pull those records with grouper=1.

Enthusiast

Re: Syntax error: INTO clause is not allowed.

if the data type is SMALLINT, you do not need the single quotes around the integer.

 

WHERE grouper = 1;