Post reply

Why can''t I do ''create table blah as (select....''?

  • January 25, 2007 at 4:13 am

    #118634

    Hi all,

    I am attempting to create a table from another table.  I thought I could do:

    CREATE TABLE NEW_ADDRESSES AS

    (SELECT * FROM ADDRESSES)

    Is this not possible? I keep getting an error 'Incorrect syntax near the keyword as'.

    Thanks,

    Paula.

  • January 25, 2007 at 4:19 am

    #685465

    Possible, but not with that syntax. Did you check books online?

    The syntax you're looking for is

    SELECT * INTO New_Addresses FROM Addresses

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2007 at 4:21 am

    #685466

    Yes, I checked books online but was looking for a 'create' statement.  In your example I presume the new_addresses table would have to exist already?

  • January 25, 2007 at 4:26 am

    #685468

    No. It has to not exist. 

    SELECT INTO creates the table and inserts the results of the select into it.

    To insert results into an existing table, you'd use INSERT INTO <table> SELECT ....

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 25, 2007 at 4:39 am

    #685474

    Thanks, that's great.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply