Results 1 to 6 of 6

Thread: [RESOLVED] create table syntax - mssql 2008 r2

  1. #1

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Resolved [RESOLVED] create table syntax - mssql 2008 r2

    Is this syntax no longer valid?

    Code:
    CREATE TABLE MYTABLE AS SELECT * FROM ANOTHER_TABLE;
    i am pretty certain it works in oracle8i and upwards. It doesn't work in MSSQL.

    The syntax that works in MSSQL is
    Code:
    SELECT * INTO MYTABLE from ANOTHER_TABLE
    is there one more way to do this in SQL Server 2008?
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: create table syntax - mssql 2008 r2

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: [RESOLVED] create table syntax - mssql 2008 r2

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: [RESOLVED] create table syntax - mssql 2008 r2

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] create table syntax - mssql 2008 r2

    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).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,226

    Re: [RESOLVED] create table syntax - mssql 2008 r2

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width