Archives of the TeradataForum
Message Posted: Fri, 12 Jan 2007 @ 12:50:15 GMT
| Subj: | | Re: ORDER BY in INSERT SELECT to GENERATE Sequence |
| |
| From: | | Dieter Noeth |
Anomy.Anom wrote:
| | When we try to do the below INSERT.. SELECT | |
> Insert into temp2 (value1, value2, value3)
> As
> Select (value1, valu2, value3) from temp1 order by value1
Did you work with MS SQL Server before?
| | We get the following error | |
| | "3706: ORDER BY IS NOT ALLOWED IN SUB QUERIES" | |
| | I understand ORDER BY can't exist in the sub query. | |
| | But our intention is to generate a sequence number (in temp2) based on the 'value1' column in some order. | |
You mean, there's an Identity column in temp2?
Even if Order By was allowed this would not result in a sequential number. It's much easier to create the sequence during Select using an
OLAP-function:
Select
row_number() over (order by value1)
value1, valu2, value3
from temp1
Dieter
| 
