Archives of the TeradataForum

Message Posted: Fri, 04 Sep 2009 @ 15:41:36 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Shri -

There are few enough circumstances where you'd want to insert ordered data in database, but why even try w/ Teradata? Tables are stored by the hash of the PI (with some additional fiddling for rows with the same hash). Just out of curiosity, if you're inserting ordered data in Oracle, why?

At any rate, even though it's totally pointless and an utter waste of time, you should be able to run your query by writing it as select top x, where x is greater than the rows you expect to insert.

     Insert into test2 (a1, a2, a3)
     select top 1000000 x, y, z from test1 order by z  -- test1 better have
                                                              <= 1000000 rows

However, if you're caught doing this, it's three points against your SQL license.

(To be fair, if you run the above, the exact order of rows in test2 may not be the same as if you just ran the insert w/o sorting. This is because rows with the same hash value may arrive in a different order, so they'll be sorted differently within that hash value. But it's hard for me to imagine a circumstance where that makes any sort of noticeable difference. So while technically true, it's irrelevant to anything you're ever likely to do.)

Dave C

	<Prev	Next>		<<First	<Prev	Next>	Last>>