Professional Web Applications Themes

Insert With Identiy - Microsoft SQL / MS SQL Server

Hello, if I do an INSERT INTO based on a SELECT with an ORDER BY can I be 100% sure that the records entered will have an incremental key on the identity column if I set one up correctly? It looks like this is going to work but something about it gives me a bad feeling! Thanks, Kevin....

  1. #1

    Default Insert With Identiy

    Hello, if I do an INSERT INTO based on a SELECT with an ORDER BY can I be
    100% sure that the records entered will have an incremental key on the
    identity column if I set one up correctly?

    It looks like this is going to work but something about it gives me a bad
    feeling!

    Thanks, Kevin.


    Kevin Munro Guest

  2. #2

    Default Re: Insert With Identiy

    > Hello, if I do an INSERT INTO based on a SELECT with an ORDER BY can I be
    > 100% sure that the records entered will have an incremental key on the
    > identity column if I set one up correctly?
    No, they are not guaranteed to be inserted in any order. This is usually
    what happens, but it is not 100% certain.


    Aaron Bertrand - MVP Guest

  3. Moderated Post

    Default Re: Insert With Identiy

    Removed by Administrator
    Kevin Munro Guest
    Moderated Post

  4. Moderated Post

    Default Re: Insert With Identiy

    Removed by Administrator
    Aaron Bertrand - MVP Guest
    Moderated Post

  5. Moderated Post

    Default Re: Insert With Identiy

    Removed by Administrator
    bruce barker Guest
    Moderated Post

  6. #6

    Default Re: Insert With Identiy

    > insert into a work table (temp table or table variable) first which has an
    > identity column.
    This is exactly what I was talking about earlier. The inserts are not
    necessarily going to happen in the same order as the select! (It is
    *usually* what happens, but it is not guaranteed!)


    Aaron Bertrand - MVP Guest

  7. #7

    Default Re: Insert With Identiy

    >> if I do an INSERT INTO based on a SELECT with an ORDER BY can I be
    100% sure that the records [sic] entered will have an incremental key on
    the identity column [sic] if I set one up correctly? <<

    No. First of all there are no records in SQL; rows are totally
    different. One of the **MANY** ways they are different is that they
    have no ordering. When you insert an entire set via a query expression,
    the engine is free to use any PHYSICAL ordering it wishes. In fact, the
    use of an ORDER BY is limited to cursors, where a set (table) is
    converted into a file (sequential structure). In Standard SQL, you
    would get an error message about this syntax.

    Next, IDENTITY is not a column; it is a property. It is related to
    PHYSICAL storage and has nothing to do with the LOGICAL data model.

    The real question is what attributes are you using to identify the
    entities in your data model?

    All of your postings are asking the wrong questions; you have not done
    your homework on RDBMS theory and techniques. You keep trying to force
    OO and traditional file processing into SQL; you even use the terminolgy
    from OO and traditional file processing!

    All you seem to want here is a pile of kludges. If you use a big enough
    hammer, you can make it limp forward, but it ain't gonna run very long
    or very far.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

  8. #8

    Default Re: Insert With Identiy

    If I have a clustered index on it then it should be ok.

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... [/ref]
    an 
    >
    > This is exactly what I was talking about earlier. The inserts are not
    > necessarily going to happen in the same order as the select! (It is
    > *usually* what happens, but it is not guaranteed!)
    >
    >[/ref]


    Kevin Guest

  9. #9

    Default Re: Insert With Identiy

    "Joe Celko" <com> wrote in message
    news:%232iSmx%phx.gbl... [/ref]
    > 100% sure that the records [sic] entered will have an incremental key on
    > the identity column [sic] if I set one up correctly?
    > No. First of all there are no records in SQL; rows are totally
    > different. One of the **MANY** ways they are different is that they
    > have no ordering. When you insert an entire set via a query expression,
    > the engine is free to use any PHYSICAL ordering it wishes. In fact, the
    > use of an ORDER BY is limited to cursors, where a set (table) is
    > converted into a file (sequential structure). In Standard SQL, you
    > would get an error message about this syntax.[/ref]

    If I set a clustered index on the column then doesn't this affect the
    PHYSICAL ordering? I think you'll find that it does and I'm rather
    surprised you haven't pointed this out to me.

    Is there such a thing as Standard SQL? Surely you mean ANSI-92 SQL?
     

    I know that, we all know that. I can be lazy at times, aren't we all?
     

    WHY is that the real question?
     

    You mean I've not read your book and I've not implemented your nested set
    theory in my hierarchy. By the way, I like your solution - I like it a lot.
    If I'd discovered it sooner I would likely have implemented it. Feel
    better? :-)
     

    Yes, I've got a broad skill base that goes back many years. I think I've
    always called 'rows' as 'records'. But I'll make a special effort from now
    on and call them rows. I no longer call 'columns' as 'fields' so I'm
    already half way there ;-)
     

    I can't see how you have enough information to make that claim but thanks
    for your post and keep making these valuable contributions :-)
     


    Kevin Guest

  10. #10

    Default Re: Insert With Identiy

    Kevin Munro (com) writes: 

    No, you cannot. However, in many cases you do get the result you are looking
    for. And you improve your chances if you add the hint OPTION (MAXDOP 1),
    to turn off parallellism. With parallellism, SQL Server could set up
    parallel streams and insert the rows from SELECT from different places.

    One thing to note is that INSERT INTO appears to be safer using SELECT INTO
    and the IDENTITY function.

    --
    Erland Sommarskog, SQL Server MVP, se

    Books Online for SQL Server SP3 at
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
    Erland Guest

  11. #11

    Default Re: Insert With Identiy

    Is this because we can't gurantee what order the data is written to the
    table?

    "Aaron Bertrand - MVP" <com> wrote in message
    news:%phx.gbl... 
    >
    > No, you're missing the point. The order is not guaranteed, no matter what
    > kind of index you have.
    >
    >[/ref]


    Kevin 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. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  3. Multiple Insert or Looping Insert
    By payado in forum Dreamweaver AppDev
    Replies: 1
    Last Post: May 25th, 09:33 PM
  4. Replies: 0
    Last Post: July 23rd, 11:30 PM
  5. Replies: 3
    Last Post: September 30th, 09:24 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