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