leebuckman (7/28/2010)
All,Thanks for the replies but i am still at a loss here. Let me explain further and simplify the query and exactly what i am trying to do. I know there is another way of doing this that does work but i want to understand 'WHY' this worked in 2000 and 'WHY' it does not in 2008 !
Why it worked in 2000 - The 2000 engine did honour the order by in various situations where it shouldn't have and didn't need to (including within views, subqueries and inserts). This was a bug, a shortcoming in the engine. Why it does not work in 2008 - changes to the query execution engine to allow it to work more optimally and it will not do sorts in cases where they are not necessary.
Remember that tables by definition DO NOT have an order. They are unordered sets of rows. Hence the order that data is inserted is irrelevent. The only time the presence of an order by affects an insert is when there's an identity column in the destination table. Then the Order By sets the sequence for that.
I want to insert into the table BB_HOLB999 the latest booking date for each customer and make use of the with ignore_dup_key to ensure the insert does not fail when it finds a duplicate customer number.
Now that's a lazy way of doing things if I ever heard of one. Why not just insert the rows that you want? That way you're not depending on undocumented behaviour which can and does change.
If you'd mentioned that in the first post, i could have given you an efficient, working solution then.
Does the query below NOT inssert the data in descending order of booking date ??
No. Order of rows in a table is meaningless, so the SQL engine is under no obligation to do the order by, or to insert the rows in any order whatsoever. Especially the case if the query runs in parallel.
Try this, which just inserts the rows that you actually need.
insert into BB_HOLB999
SELECT syst, CREF, bkbdat FROM
(select 'OSCAR' as [syst], CREF, bkbdat, ROW_NUMBER() OVER (PARTITON BY CREF ORDER BY bkbdat DESC) AS BookingOrder
from BB_TABLE join refdata.dbo.TDFBK on CREF=BKCREF
where BKCOST > 0
and BKPAX > 0
and BKINV1 > 0
and BKLINK is NULL
and bktype is NULL
and BKBSTS = ''
and BKLAPD is null
and BKBDAT<dmat
and (BKDEPT in ('20','23','16','50','10','40','52','80')
or (BKDEPT ='30' and BKPROD in ('Qp','39','8p','8q','8r','8s','8t','8u','8v','8w','8x','1A','9d','X2','AG','AH','AF','AI','6N','6A','8N','V1','U1')))
) sub
WHERE BookingOrder = 1
go
That will insert just one row per CREF, the one with the latest booking date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability