Post reply

problem in Insert INTO using a select statement

  • June 19, 2006 at 7:22 am

    #115554

    Hi,

    I am trying to insert somevalues into a temp table using a select statement. I want to insert those ordered by "firstname". But it errors saying "Incorrect syntax near the keyword 'ORDER'."

    The statement im using:

    DECLARE @tbl_TempUser_1 TABLE(

    [UserID] [int] NOT NULL ,

    [DisplayName] Varchar(150),

    [FullName] varchar(300)

    )

    INSERT INTO @tbl_TempUser_1(UserID, DisplayName, FullName)

    (SELECT u.userid, u.displayname, u.FirstName + ' ' + u.LastName as fullname FROM tbl_user u

    LEFT JOIN tbl_usercoach uc ON

    u.userid=uc.userid

    WHERE (uc.coachid = 3 AND u.active = 1) ORDER BY u.FirstName desc)

    Any help is appreciated.

    Thanks

  • June 19, 2006 at 7:57 am

    #644588

    You don't need the brackets around the select. Also the Order By is not necessary in an insert. The concept of order of rows in a table is meaningless. Order the rows when you select them, not when you insert them.

    INSERT INTO @tbl_TempUser_1(UserID, DisplayName, FullName)

    SELECT u.userid, u.displayname, u.FirstName + ' ' + u.LastName as fullname FROM tbl_user u

    LEFT JOIN tbl_usercoach uc ON

    u.userid=uc.userid

    WHERE (uc.coachid = 3 AND u.active = 1)

    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

Viewing 2 posts - 1 through 1 (of 1 total)

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