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 |
|
chih
Posting Yak Master
154 Posts |
 Posted - 2008-04-11 : 02:21:25
|
| Hi Everyone,I insert data to #temp table by using 'select into'. Then I insert one extra row. when I select data, why it does not follow the order?(the last insert should be in the last row but it becomes the first row)Here is the simple scriptSelect name,code,dates into #temp From member Order By DatesInsert #temp (name,code,dates)select 'dave', '0', getdate()select * from #temp |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-11 : 04:40:13
|
| Because insert does not guarantee that data will be inserted in any particular order. You need to use ORDER BY while selecting data to force certain order.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2008-04-13 : 19:45:01
|
I actually will do some update statements and want to get the last row data. so it is important to insert the data in the order. Istead of useing select into, I know I can create a temp table and insert data to it. But it will require extra scripts. quote:
Originally posted by harsh_athalye
Because insert does not guarantee that data will be inserted in any particular order. You need to use ORDER BY while selecting data to force certain order.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
|
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-04-13 : 20:26:08
|
If you add an identity column and use a clustered index on it, the insert will always be the last row. But, as some have suggested already, that's not the way an RDBMS is supposed to work and depending on it working in such a fashion is a mistake. You should use ORDER BY to guarantee the order.--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-13 : 23:36:20
|
| If you have an IDENTITY column in your temp table, you can get the last inserted row by retrieving the row having MAX(IdColumn) from the table.and if you are using this to populate your tableInsert #temp (name,code,dates)select 'dave', '0', getdate()you can use SELECT TOP 1 * FROM YourTable ORDER BY dates desc to get last row. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2008-04-14 : 12:38:57
|
Or, with an IDENTITY, you could use SELECT * FROM YourTable WHERE ID = SCOPE_IDENTITY() ... no need for the rather expensive ORDER BY or MAX.--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] |
|
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2008-04-14 : 19:02:32
|
| Thanks everyone,I use union to combine two insert statements. It works fine now. |
|
|
|
|
|
|
|
|
insert temp table problem