| Author |
Topic |
|
Emily_2
Starting Member
9 Posts |
 Posted - 2007-04-25 : 15:52:36
|
| Hi All,From what I know, when you execute an INSERT INTO table1 (field0)SELECT field0 FROM table 2 ORDER BY field0the rows are inserted in whatever order the server engine thinks is better at that moment. Is it any way I can have field0 in table1 inserted in the order I need?My problem is that I can not touch table1, it is used by a legacy application which I am not allowed to modify. I was thinking about creating a clustered index, adding an id field, etc, but I can not know how this will affect the front end application.table1 and table2 have only a few hundred records.table1 = CREATE TABLE [FinalPlasma] ([name] [varchar] (2000) NULL ) table2 = CREATE TABLE [Test_FinalPlasma] ([nameID] [int] NOT NULL , [name] [varchar] (2000) NULL ) The update that is not giving the "good" order = DELETE FROM FinalPlasmaINSERT INTO FinalPlasma SELECT name from dbo.Test_FinalPlasmaUnfortunately I can not order the [name] field after the update, because it looks something like Donald McQ. ShaverMark SheildsR.J. ShirleyW.E. SillsKenneth A. SmeeA. Britton SmithLCol Edward W. SmithHarry V. SmithM. E. SouthernTimothy A. SparlingSpectrum Investment Management LimitedThank you, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 16:01:43
|
| Well it is inserting the data into your table in the order specified since you have an order by. Meaning that the select query runs first with the order by and each row is inserted into your table in the order of the result set. However, in order to guarantee the order when getting the data out of the table via a select, you must specify an order by.Adding an identity or a clustered index does not change this. You still need to specify an order by in your select if you want the data ordered in your result set.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Emily_2
Starting Member
9 Posts |
Posted - 2007-04-25 : 17:03:23
|
| Tara, thank you for the quick response,The table FROM which I pull data for insert is already ordered the way I need. So the source table is “good” but when I do the Insert into the DESTINATION table the order gets changed. I can not put an Order By, because the source table has an arbitrary order of the “name” field. This is the way I inherited it, and I must reproduce the exact order. For example, the SOURCE table is like this:Table Test_FinalPlasma, with a single field [name], unindexed,this is how the records look like in a simple SELECT * FROM Test_FinalPlasma, without any Order By:nameID ….name 1 John L. Adams2 Clive Addy3 Airforce Officers Advisory Group4 Vadm John Allan5 Thomas S. AllanThey kind of have an order by last name visible to a human, but not to transact sql. This is the way the records were manually put in the table, long ago. What I need to do is :INSERT INTO FinalPlasma SELECT name from Test_FinalPlasmaPreserving the existing (arbitrary) order of the records of table FinalPlasmaWould a rowId and a cursor fetch help (I’ll put it into a sp)? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-25 : 17:10:34
|
| Use the primarykey in the order by..looks like nameID is the PK here.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-25 : 17:34:29
|
| Well, come to think of it, the order in which you see the records in the table may not always be the same. So unless you provide an ORDER BY in your query, there is no particualr order in which data is stored in the table. So, as much as you put all the efforts in inserting row after row, they could end up getting mixed up in the table.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Emily_2
Starting Member
9 Posts |
Posted - 2007-04-25 : 18:21:40
|
| Yes, the source table has an id field, but the DESTINATION has not! So even if the rows are retrieved nicely ordered, they are inserted randomly into the destination. This is per SQL Server specifications, but … can I circumvent this somehow? Tara, even when i used INSERT INTO destination SELECT name FROM source ORDER BY nameID the records were inserted randomly into destination. What am i missing?I was thinking about dropping the destination table, entirely, then recreating it again, maybe this way the pages / leafs will flow better. …unfortunately I can not test this right now, I only have access to this old server at night …Unrelated question: How can one use tabs when posting on this forum? Messages look much nicer with char(9)…Thank you all |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 18:30:41
|
| You aren't missing anything. There is no order! That's just how it works. The select portion of your insert would have order the result set, but there's no way to know how SQL Server is going to store the information. So even if the rows were being inserted in order, they aren't stored in order. The only way to guarantee an order is to use an order by in your select when returning a result set. "The only way" is the key part.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-04-25 : 18:36:41
|
| There is no such thing as an ordered table. Relational thoery tells us that a table, view, etc.. is a Set and a Set by definition is an unordered collection.From BOL:ORDER BY is important because relational theory specifies that the rows in a result set cannot be assumed to have any sequence unless ORDER BY is specified. ORDER BY must be used in any SELECT statement for which the order of the result set rows is important.So the order of the data is the table is moot, because there is no guarantee of the order the data comes out of the table unless we use ORDER BY.-Ryan |
|
|
|
Emily_2
Starting Member
9 Posts |
Posted - 2007-04-25 : 19:09:58
|
| I hear you Tara, the only way ...I’d love to use it, but I do not have the time/permissions to modify the back-end app.Still, the question is:HOW did they manage to populate this ORIGIN table, named [FinalPlasma], having one single field [name], which has NO indexes, clusters, nothing at all ... , yet the records are there, in the "expected" order? If I remember correctly, this was a dedicated server, had windows installed on, than SQL, than the app.exe, and the tables were supposed to be static, populated once ... never to be touched again...If I could answer to the question:How did they manage to physically create this table the way it is, may be I could figure a solution to my problem.Could a job do this, on a pristine database, with CREATE TABLE immediately followed by an Import from Excel? If the answer is yes, I might resort to doing the same, select data, export to excel, import from file ... not elegant ... but I really need some alternative... |
|
|
|
Emily_2
Starting Member
9 Posts |
Posted - 2007-04-25 : 19:14:37
|
| Ryan,please take a look at the sample rows in my first message ...they are the result of a simple select(without order by)how did they manage to place these rows so nicely in the table, to begin with (i mean the first time ever)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-25 : 19:21:40
|
| You are missing the point. Their data is not stored in sorted order. It's not possible.Their data looks like this according to you:1 John L. Adams2 Clive Addy3 Airforce Officers Advisory Group4 Vadm John Allan5 Thomas S. AllanIf you run a select against this source table and it returns it ordered by the int column and you don't specify an order by, then it just so happens that SQL Server found the rows in that order. You aren't guaranteed to always get 1,2,3,4,5. You may get 1,3,4,5,2 or any other variation. Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-04-26 : 07:34:49
|
| to answer your question: How did they manage to physically create this table the way it is?They probably made the table [FinalPlasma], and used DTS to shove an excel file into it. i am guessing that app.exe selects by row number instead of a key [sarcasm]great design [/sarcasm]. Yes, they were relying on the database to return the data in the same order, all the time. I do not envy you in fixing this.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
Force an INSERT INTO to insert rows orderly