Printable View
Is this syntax no longer valid?
i am pretty certain it works in oracle8i and upwards. It doesn't work in MSSQL.Code:CREATE TABLE MYTABLE AS SELECT * FROM ANOTHER_TABLE;
The syntax that works in MSSQL is
is there one more way to do this in SQL Server 2008?Code:SELECT * INTO MYTABLE from ANOTHER_TABLE
That is ORACLE syntax and it's mixing DDL and DML syntax (CREATE is DDL and SELECT is DML) - yuk.
MS SQL has the INTO keyword - which you found already - and from what I know is the only way to make a table from a select.
Interesting thing about standards is that they often say that you have to implement a feature (in this case, creating a table based on the results from a select statement) but not how (a good example of this is HTML... and why something looks one way in IE but differently in FF or Chrome, etc)... Oracle and MS went down different roads in this case. the "S" in SQL stands for "Structured" ... not "Standard"... people tend to forget this.
-tg
tg,
one reason is that none of the vendors (microsoft / oracle / ibm included) have certified beyond the ANSI-92 entry level standard. As a matter of fact, NIST doesn't even certify anymore.
I feel that CREATE is a single keyword statement - takes an OBJECT name - and lots of possible parameters/arguments in ()'s.
SELECT is a "free text" parsed-like command string...
I personally like INTO - and the more recently added OUTPUT - keywords that you can use with SELECT and UPDATE and DELETE statements...
Makes more sense to me...
btw - I started doing SQL back in the Sybase mainframe days - so I'm kind of slanted towards the Sybase/MS syntax.
Dealing with dates in Oracle - like today and TODATE and formatting - wow - so other-worldly (imo).
I have been doing Oracle since 2002. Prior to that I worked exclusively with SQL Server and MS Access.
I now find the pl-sql syntax more natural than sql.
There was a brief period, when I was a card-carrying member of the I-hATE-Oracle club, but over a period of time I have changed my views.