SQL (DB2) Creating table by selecting from other table

Question

I´ve read several discussions and websites about creating a SQL query to create a table by selecting data from other table, but none of them solved my issue. I don´t know what else to do.

I am working on a SQL script to run a sequence of selects and creates do resume some data. I´ve been using this SQL queries on a SaS server using DB2 data. Now I need to migrate to Dbeaver to using other sources.

I just want to create a table by selecting some columns and data from other table, for this simple example :

"CREATE TABLE DB2XXXX.PaidResume AS 
(SELECT HistoricalPaid.AccountNumber AS CONTA 
FROM DB2XXX.HistPaid HistoricalPaid 
WHERE HistoricalPaid.AccountNumber = 'XXXXX');

All I got it this error

Error occurred during SQL query execution

SQL Error [42601]: ILLEGAL SYMBOL "<END-OF-STATEMENT>". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.19.26

If I just exclude the "CREATE TABLE DB2XXXX.PaidResume AS" and run the select only, it works.

Guys, I am on vacation so I couldnt test your answers. Thanks anyway. I will feedback later. To clarify, I am using SAS and DBeaver. I both I work with SQL codes. My code works on SAS but not on DBeaver. Both are gathering data from a DB2 server. — FábioRB
– FábioRB, Commented Apr 12, 2022 at 17:07

The Impaler · Accepted Answer · 2022-04-01 15:48:22Z

2

You must include WITH DATA or WITH NO DATA at the end of the SQL statement. For example:

create table u as (select a from t) with data;

See example at db<>fiddle.

answered Apr 1, 2022 at 15:48

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nfgl Over a year ago

I think the OP are using DB2 for Z/OS (because of the 'ILLEGAL SYMBOL...' text) where WITH NO DATA seems to be the only option doc. they could use a table temporarily defined as a MQT (I cannot test, no access to Z/OS) here's a db<>fiddle

FábioRB Over a year ago

Thanks man, using "with no data" worked. Using "with data" returns the same error.

Collectives™ on Stack Overflow

SQL (DB2) Creating table by selecting from other table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related