Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gman6
Starting Member
4 Posts |
 Posted - 2008-05-19 : 11:27:35
|
| I am performing a Select Into from a #table into a real table that has a surrogate key. If this is in a transaction (or not in one) am I guaranteed that the records inserted will be sequential surrogate key ids? Select * into REALTABLE from MYPOUNDTABLE --40 rowsCan I assume that if the first one inserted is id 32 that the last one is 72? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-19 : 13:02:50
|
| Why are you concerned about order in which records are inserted? You can always retrieve the records in the order you want by specifying an order by in your select query. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-05-19 : 13:44:01
|
| Nope, no guarantee. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-19 : 14:37:21
|
| Your question is a little confusing. Are you performing a:1) SELECT surKey as identity(int,1,1), <colList> INTO <NewTable> FROM #temp ORDER BY SurKeyor is it:2) INSERT <existingTableWithIdentity> (<colListExcludingIdentityCol>) SELECT <colList> FROM #temp ORDER BY SurKeybecause method 2) will guarantee the row order will be in sync with the identity sequence, method 1) won'tBe One with the OptimizerTG |
|
|
|
gman6
Starting Member
4 Posts |
Posted - 2008-05-26 : 23:03:42
|
quote:
Originally posted by TG
Your question is a little confusing. Are you performing a:1) SELECT surKey as identity(int,1,1), <colList> INTO <NewTable> FROM #temp ORDER BY SurKeyor is it:2) INSERT <existingTableWithIdentity> (<colListExcludingIdentityCol>) SELECT <colList> FROM #temp ORDER BY SurKeybecause method 2) will guarantee the row order will be in sync with the identity sequence, method 1) won'tBe One with the OptimizerTG
#2 is the approach however parallelism will cause problems I found out, MS even put out an article describing the problem....[url]http://support.microsoft.com/kb/273586[/url] |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-05-27 : 07:30:57
|
| The article supports what I said: method 2 will guarantee that the identity sequence will match the order of the SELECT with ORDER BY. The sequence mismatching can happen with method 1: SELECT identity() INTO...Have you tried method 2 and have a problem with it? If so, please post your code.Be One with the OptimizerTG |
|
|
|
|
|
|
Surrogate Key population on a Select Into