Professional Web Applications Themes

Problem with IDENTITY and ORDER BY in a INSERT statement - Microsoft SQL / MS SQL Server

Peter, I think this is a minor bug with the SQL Server pr. I've passed it on to MS. Fortunately the workaround is simple. I'll also mention that the behavior you seem to be looking for (an "ordered" insert) is poorly doented at best. Technically, one should not be able to influence any sequence in which an INSERT query inserts rows into a table. The SELECT that follows the INSERT should simply define a set of rows, and in that sense, the ORDER BY is contributing nothing. On the other hand, it seems to work, but I wouldn't be surprised ...

  1. #1

    Default Re: Problem with IDENTITY and ORDER BY in a INSERT statement

    Peter,

    I think this is a minor bug with the SQL Server pr. I've passed
    it on to MS. Fortunately the workaround is simple. I'll also mention
    that the behavior you seem to be looking for (an "ordered" insert) is
    poorly doented at best. Technically, one should not be able
    to influence any sequence in which an INSERT query inserts rows
    into a table. The SELECT that follows the INSERT should simply
    define a set of rows, and in that sense, the ORDER BY is contributing
    nothing. On the other hand, it seems to work, but I wouldn't be surprised
    if it did not in some situation with the right combination of parallelism,
    concurrency, or indexes.

    Steve Kass
    Drew University

    Peter Bengtsson wrote:
    >Hi, "By design..."
    >This works aswell:
    >INSERT INTO MyTemp1 (Surname,Firstname) SELECT SURNAME,
    >FIRSTNAME from MyTemp2 order by rank
    >
    >I'm still curius to find out why SQL Server haves this
    >behavior.
    >
    >Mvh
    >Peter
    >
    >
    >>-----Original Message-----
    >>Hi
    >>I get the error:
    >>Server: Msg 8101, Level 16, State 1, Line 20
    >>An explicit value for the identity column in
    >>table 'MyTemp1' can only be specified when a column list
    >>is used and IDENTITY_INSERT is ON.
    >>
    >>SQL to reproduce the error:
    >>--------------------------------
    >>DECLARE MyTemp1 TABLE (MyTemp1_ID INT IDENTITY ,SURNAME
    >>VARCHAR(50) ,FIRSTNAME VARCHAR(50))
    >>
    >>DECLARE MyTemp2 TABLE (MyTemp2_ID INT ,SURNAME VARCHAR
    >>(50),FIRSTNAME VARCHAR(50),RANK INT)
    >>
    >>Insert into MyTemp2 VALUES(1, 'a', 'a', 1)
    >>Insert into MyTemp2 VALUES(2, 'b', 'b', 3)
    >>Insert into MyTemp2 VALUES(3, 'c', 'c', 2)
    >>Insert into MyTemp2 VALUES(4, 'd', 'd', 5)
    >>Insert into MyTemp2 VALUES(5, 'e', 'e', 4)
    >>
    >>INSERT INTO MyTemp1 SELECT SURNAME, FIRSTNAME from
    >>MyTemp2 order by rank
    >>
    >>select * from MyTemp1
    >>---------------------------------------
    >>
    >>If I add a dummy column at the end to the first table
    >>everything works fine (or remowe the order by):
    >>
    >>DECLARE MyTemp1 TABLE (MyTemp1_ID INT IDENTITY ,SURNAME
    >>VARCHAR(50) ,FIRSTNAME VARCHAR(50), anything datetime)
    >>
    >>1 a a NULL
    >>2 c c NULL
    >>3 b b NULL
    >>4 e e NULL
    >>5 d d NULL
    >>
    >>Whats going on ? Is it possible that a hidden value from
    >>the sort operation mix up the query plan ?
    >>
    >>Mvh
    >>Peter
    >>.
    >>
    >>
    >>
    Steve Kass Guest

  2. #2

    Default Re: Problem with IDENTITY and ORDER BY in a INSERT statement

    Thanks !
    I agree, "ordred" inserts should not be considred as
    supported functionality (Let SQL Server storage engine
    handle the job). The examle was very basic, but still
    pratical. I can afterwards use the id (identity) of
    MyTemp1 for uniqe identification of rows and still know
    that there is an order int the table (i.e order by
    country) but the order by column would not it self give
    uniqe values. This can be solved in many ways (cursors
    etc.), but in this case the intention was to use the temp
    table later on in a "advanced" SP.

    Thanks again, your answer took my design to another
    direction.

    Mvh
    Peter
    Peter Bengtsson Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. problem with insert statement
    By sviolet in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: June 23rd, 01:39 AM
  3. Replies: 3
    Last Post: September 30th, 09:24 PM
  4. Replies: 4
    Last Post: September 16th, 04:54 PM
  5. Insert Statement help
    By ksbrace in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 02:04 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139