2

So in Db2/IBM Data Studio I am unable to run this:

SELECT * 
INTO newtable 
FROM oldtable

I am getting this error message:

The "newtable" is not valid in the context where it is used..sqlcode=-206 sqlstate=42703, driver=3.68.61.

The table newtable does not exist already. I researched the sqlcode, sqlstate - IBM error code resolve, but it didn't help.

To clarify I want to make a copy of the oldtable as a new table newtable.

I know there are alternate ways to do this same thing but this one is the simplest and I'm wondering why it's not working.

Thank you for your time.

Matija Sreckovic
  • 85
  • 2
  • 7
  • It's not working because such syntax is incorrect. – mustaccio Jun 09 '18 at 14:47
  • @mustaccio [Here](https://www.w3schools.com/sql/sql_select_into.asp) is where I found the syntax. What do you think is correct? – Matija Sreckovic Jun 09 '18 at 16:31
  • Why would anyone down vote this and not leave a comment – Matija Sreckovic Jun 09 '18 at 18:04
  • Consider using product documentation (for your specific Db2 version and platform) as a reference; SQL dialects differ between DBMSes. – mustaccio Jun 09 '18 at 19:14
  • Another good example why w3fools is not a resource to rely on. That is not even close to standard SQL. In standard SQL (and [in DB2 as well](https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html?pos=2)) you need to use `create table newtable as select * from oldtable` –  Jun 09 '18 at 19:38
  • @a_horse_with_no_name That doesn't work either. I can only make a new empty table (WITH NO DATA) and then insert data with INSERT INTO – Matija Sreckovic Jun 10 '18 at 08:23
  • You need to be using Db2 11.1 to use the WITH DATA clause – Paul Vernon Jun 11 '18 at 11:53
  • No thats not correct @P.Vernon. Its available in earlier versions as well. I use it (V7.3 ) –  Jun 11 '18 at 17:10
  • I was assuming Db2 for LUW (Linux, Unix, Windows). V.7.3 is Db2 for i. They are different products and have different version numbers – Paul Vernon Jun 12 '18 at 14:07

1 Answers1

2

Select into is for SQL Server (or orther in dont know). For DB2 instruction for save query into table is :

create table newtable as (
SELECT * FROM oldtable
) with data

You can find more options here

Esperento57
  • 16,521
  • 3
  • 39
  • 45