Incorrect syntax near the keyword 'as'

  • I know its simple but I am not able to understand this error I got this error when I tried creating an exesting table which has huge data.

    To make is easy i tried using sample table

    I created a sample table

    create table temp1 (

    ser_no integer );

    when I tried creating another table using below statement

    create table temp2 as (select * from temp1 );

    I am getting below error

    "Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'as'"

    Do you think any grant issues.

  • sql_time (10/25/2009)


    ...

    when I tried creating another table using below statement

    create table temp2 as (select * from temp1 );

    I am getting below error

    "Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'as'"

    SQL Server has very good error messages. All you have to do is read the error message and trust that it is both sufficient and correct.

    It's telling you that something near the 'as' is incorrect syntax. In this case, it's that the 'as' is incorrect and does not belong in that statement:

    create table temp2 (select * from temp1 );

    Of course that will just lead to another error, because a CREATE TABLE statement does not accept a dataset. If you are trying to Create a table using another table's definition, do it like this:

    SELECT *

    INTO temp2

    FROM temp1

    WHERE 1=0 -- <-- remove this line if you want the data also

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • T Q RBarryYoung it worked

  • Glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

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

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