Insert..Select statement problem

Hi,

I am trying to run the following insert statement, but am gettng an error. The table I want to insert to
(DimensionMonthTime_hold) has an
Identity column defined for its key.

The part I cannot figure out is that when I run this insert statement without the order by statement, the insert is successful. If I attempt to run this insert statement with the Order By statement, I get an error saying that I have to provide a value for the identity value in the insert statement (which i don't want to do.) I need to have the data sorted, hence the reason for the order by.

I've tried to specify the column names on the insert line, but haven't figure that out.

Any suggestions?

Thanks

Jim

----------------------------

Insert into DimensionMonthTime_hold
select distinct substring(period,1,4) + substring(period,6,2),
substring(period,1,4) ,
case substring(period,6,2)
when '01' then '1'
when '02' then '1'
when '03' then '1'
when '04' then '2'
when '05' then '2'
when '06' then '2'
when '07' then '3'
when '08' then '3'
when '09' then '3'
when '10' then '4'
when '11' then '4'
when '12' then '4'
else 1
end,
substring(period,6,2),
case substring(period,6,2)
when '01' then 'First Quarter'
when '02' then 'First Quarter'
when '03' then 'First Quarter'
when '04' then 'Second Quarter '
when '05' then 'Second Quarter'
when '06' then 'Second Quarter'
when '07' then 'Third Quarter '
when '08' then 'Third Quarter '
when '09' then 'Third Quarter '
when '10' then 'Fourth Quarter'
when '11' then 'Fourth Quarter'
when '12' then 'Fourth Quarter'
end,
case substring(period,6,2)
when '01' then 'January'
when '02' then 'February'
when '03' then 'March'
when '04' then 'April '
when '05' then 'May'
when '06' then 'June'
when '07' then 'July '
when '08' then 'August '
when '09' then 'September '
when '10' then 'October'
when '11' then 'November'
when '12' then 'December'
end,
1,
getdate()
from transaction
where Account_type ='A'

------------------------

In an Insert Into ... Select ... you cannot use an Order By statement for the Select. It does not make any sense anyway as it does not have any effect on the order SQl Server stores the records in your result table.

BTW: It is good practice to specify the column list in the Insert statement. This will avoid such kind of error message.

Actually, you can use Order by in an Insert ... Select statement. If the table you are inserting into doesn't have any pre-defined sorting rules stating otherwise, then the data will be in the order you specified.

This is especially true if the table has a clustered identity filed that is being autopopulated by SQL Server when you insert the data.

Thank you for your responses,

I did figure out how do what I wanted to do.

The order by statement has be the same as the select distinct statement
i.e.


-
order by substring(period,1,4) + substring(period,6,2),


Jim

Actually, you can use aliases in Order By statements. This will make your query more efficient as it won't have to do everything twice.

Select substring(period,1,4) + substring(period,6,2) As Field1, ....
.....
order by Field1, ....

Shame on me for giving wrong information. :-(

However, I do not like the idea of sorting the data on insert because the physical order of records depends on the clustered index of he table and the logical order depeds on any "order by" you apply when selecting data out of the table.

I agree, for the most part. The one exception that comes to mind is when I'm inserting data into a new table. If it can be done efficiently, I'll insert the data in the same order as the index will be in and then add the index after the insert.