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 |
| ||When we try to do the below INSERT.. SELECT|| |
> Insert into temp2 (value1, value2, value3)
> 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
row_number() over (order by value1)
value1, valu2, value3