>>It does not matter which row to keep and which row to avoid.

OK. So, for these 2, maybe you can answer some very specific questions to get this wrapped...

With Excel datasources, DTS reads the first N rows of the sheet to sample the data and makes assumptions about the data. Sorry, can't recall what number N is, but...

Views should not contain an Order By.

If you need a resultset to be ordered a certain way, always use an Order By on your Select statement.

>>is there any possibilities of getting more than one row

It depends. w_id is the primary key of a table.

Your sub-query selects from a view. Since we don't know what your...

>>IS there any hidden difference in replacing IN operator with = operators in these queries ?

Yes, there is a difference and it depends on the subquery:

SELECT cl_re_to_id from v_weeks...

>>Records are inserted into the Return table in order, and when the table function is called it appears to provide the records in that same order.

This will come...

To add to the good advice already posted ...

>> I stripped it bare trying to eliminate all possible suspects.

Does "bare" include dropping all indexes ?

Are there any other concurrent processes using...

Inability to use ORDER BY in a view in SQL2K5 highlights the danger of building a solution on something that "works, but is unsupported".

SQL2000 and earlier versions should never have...

What sort of DTS task is this ? Data Transformation ? Data-driven Query ?

If in doubt, turn on SQL Profiler and see what SQL statements are being sent to the...

>>The insert occurs in a DTS package, I'll have to go check.....

I would guess it's auto-committing every 1000 rows (the default in DTS), which is why you're seeing that behaviour.

Does column Code contain unique ascending sequence of values ?

Is the insert of 200K records going in as 1 batch, so that all 200K records are in the 'inserted' virtual table ?

Or are they individual inserts, 1 record at...

You truncated DailyAccountsDownload.

But you didn't truncate DailyAccountsDownload_Tracking, right ?

So, presumably your LEFT JOIN on DailyAccountsDownload_Tracking only evaluated "WHERE hs.MMS IS NULL" to TRUE on 25,000 of the records. Apparently the...

Select ClientID,

  min_date_in,

  Case When max_date_out = '01 Jan 2079' Then Null Else max_date_out End

From (

  Select ClientID,

    Min(date_in) As min_date_in,

    Max(IsNull(date_out, '01 Jan 2079')) As...

If the INSERT worked standalone, maybe the IF statement is "iffy" ?

Speaking of which, you never need to COUNT() an entire resultset, if all you care about is that the...