Oracle and Sybase compatibility for create table new_table as

I am trying to write an SQL query which needs to be compatible on both a Sybase and Oracle database. The query looks like the following :

SELECT * 
  INTO new_table
  FROM other_table

This query is working great on a Sybase database but not on an Oracle one. I found the equivalent for Oracle :

CREATE table new_table AS
SELECT * 
  FROM other_table

Is there a way to write a third query that would do the same and that can be executed on a Sybase and on an 开发者_开发百科Oracle database?

---

As you found, Oracle supports INTO but doesn't use it like Sybase/SQL Server do. Likewise, Sybase doesn't support Oracle's extension of the CREATE TABLE syntax.

The most reliable means of creating a table & importing data between the systems is to use two statements:

CREATE TABLE new_table (
  ...columns...
)

INSERT INTO new_table
SELECT * 
  FROM OLD_TABLE

Even then, syntax is different because Oracle requires each statement to be delimited by a semi-colon when TSQL doesn't.

Creating a table & importing all the data from another table is a red flag to me - This is not something you'd do in a stored procedure for a production system. TSQL and PLSQL are very different, so I'd expect separate scripts for DML changes.

---

There is no query that would do what you want. These environments are very different.

---

It is possible.

SELECT * INTO new_table FROM existing_table;

Thanks