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 !

What i want to do (in English) :

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.

use userdata

go

drop table BB_HOLB999

go

create table BB_HOLB999 (

SYST varchar (10),

CREF decimal (10),

BDAT decimal(7,0))

go

create unique clustered index BI on BB_HOLB999(CREF,syst) with ignore_dup_key

go

Does the query below NOT inssert the data in descending order of booking date ??

e.g. for our example of customer 8458923 who has booking dates as follows :

990401

1050912

930604

i would expect the 1050912 record to be inserted first and then because of the ignore_dup_key i would expect the next 2 records to be ignored. However, i can run this multiple times and sometimes i would get 1050912 in the table BB_HOLBB999 and sometimes i will not, hence, my question, what is going on ??

insert into BB_HOLB999

select 'OSCAR' as [syst], CREF, bkbdat

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')))

order by bkbdat desc

go

Interestingly, if i specify the insert for just customer 8458923, it is correct every time. ie change the above query to the one below (so the order by appears to work !):

insert into BB_HOLB999

select 'OSCAR' as [syst], CREF, bkbdat

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')))

and bkcref=8458923

order by bkbdat desc

go