March 4, 2009 at 9:33 am
Hi,
Let's say I have some xml with a number of places. Each place has a few attributes and each place contains one or more items. I want to insert this information into my Place table in my database. Ideally, I would like to insert all the places at once using an Insert Select statement. Then using the Output clause I can return the value of the identity column that has been created as a result of the inserts.
Once this is done, I would like to associate the new identity values with each correct Place. That way, when I insert the items, I will make sure they are associated with the right place.
I see a few possibilities here. If the rows are inserted in the order in which I select them, I can simply assign the new identity place ID sequentially (i.e. the first identity column value returned cooresponds to the first place in my xml). Or if the order is not guaranteed, I may have to compare the values of all the other columns to the xml values to ensure it is indeed the cooresponding place.
My questions:
1) When inserting multiple rows, is there any guarantee that they are inserted in the order in which they are selected in?
2) If they are not, is there a way to match up the newly inserted place in the table to the place in the xml without comparing all columns (since there is no other unique column)?
Attached is some sample code to help illustrate the issue. (BTW couldn't get any XML text to show up in this post, any idea how to escape the greater than/less than symbols in this editor)
Thanks,
Pete
March 4, 2009 at 9:38 am
Peter (3/4/2009)
1) When inserting multiple rows, is there any guarantee that they are inserted in the order in which they are selected in?
No but, if I recall, an Order By in the select does guarantee the order of the identity column.
Test it, I can't remember exactly where I heard that or if it was only certain conditions.
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
March 4, 2009 at 10:00 am
Hi Gail,
Thanks for the reply. I found the KB article for SQL Server 2000. I am using SQL Server 2005. I would guess that it also applies.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273586">
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273586
Near the bottom it shows an example of an INSERT INTO SELECT statement similar to what I am trying to do. It seems to imply that the identity column will be generated in the same order as my SELECT as long as I use an ORDER BY clause.
It then mentions this doesn't guarantee the physical order but since I am using the identity column as a primary key I would assume that it would in this case.
Thoughts?
March 4, 2009 at 10:17 am
Peter (3/4/2009)
It then mentions this doesn't guarantee the physical order but since I am using the identity column as a primary key I would assume that it would in this case.
Not necessarily, but the physical order is immaterial.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply