3

I'm trying a simple SQL select into statement on teradata tables, and following the syntax I found here. The statement is the following:

 select *  into DBNAME.account_backup  from DBNAME.account;

When I run this code I get the following error:

SELECT Failed. 3706: Syntax error: INTO clause is not allowed

The two tables have precisely the same format (I copied the SQL which created the first table and pasted it to create the second, changing only the name).

Any ideas?

Much gratitude

Rookatu
  • 1,487
  • 3
  • 21
  • 50

2 Answers2

4

SELECT INTO is SQL Server syntax, in Standard SQL syntax it's only used in Stored Procedure to put the result of a query returning a single row into a variable.

This is an equivalent query in Teradata:

create table DBNAME.account_backup as DBNAME.account with data;
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • `create table .. as select ..` is also defined in the SQL standard –  Apr 30 '15 at 21:36
  • My first column here is an identity column, and I'm getting the error "CREATE TABLE AS currently does not support Identity Columns" ... google isn't much help on this; any ideas? Thanks so much! – Rookatu May 01 '15 at 13:04
  • No workaround, CREATE TABLE AS doesn't support identity columns (most DWHs don't use IDENTITY, this is usually a relict from porting from other DBMSes). – dnoeth May 01 '15 at 13:17
  • You can create the table as ` without data` to get the structure. Then you can `insert into () as select from `. – Andrew May 01 '15 at 14:33
  • Is there any way to copy over that identity column? I do need that. Thanks! – Rookatu May 01 '15 at 14:59
  • Assuming you want to preserve the values in the identity column, then yes. After you create the new table (using `without data`), but before you insert, just drop the identity attribute: `alter table drop IDENTITY`. To be clear, you cannot preserve the values in the existing table AND keep the column as an identity column in the new table. – Andrew May 01 '15 at 15:22
  • @Andrew: `WITH NO DATA` also fails, same for `CREATE TABLE AS SELECT`. I don't think there's a workaround besides rebuilding the table CREATE TABLE statement without identity (using SHOW TABLE or a query to create it from metadata) and then INSERT/SELECT. – dnoeth May 01 '15 at 17:15
  • @dnoeth, you are correct. Identity columns drive me nuts. – Andrew May 01 '15 at 18:14
  • SQL Server for the developer productivity win! – Matt Jul 19 '17 at 21:00
-1
.export data file=output.txt;
.set recordmode off;
//query
.export reset;
fin
  • 77
  • 1
  • 3
  • The OP was asking how to copy a table, not how to export it in BTEQ. – dnoeth Jan 18 '21 at 13:53
  • Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Mark Rotteveel Jan 18 '21 at 16:17