Posted by Erland Sommarskog on 11/29/07 22:03

metaperl (metaperl@gmail.com) writes:
> Far below (in section "original 3 steps"), you see the following:
> 1. a temp table is created
> 2. some data is inserted into this table
> 3. some of the inserted data is removed based on a join with the same
> table that the original select was made from
>
> In my opinion, there is no way that the join could produce more rows
> than were originally retrieved from viewD. Hence, we could get rid of
> the DELETE step by simply changing the query to be:
>
> INSERT INTO #details ( rec_id, orig_corr, bene_corr )
> SELECT rec_id, 0, 0
> FROM viewD
> WHERE SOURCE_SYS NOT IN ( 'G', 'K' )
> AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )
> AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )
> AND INMESS NOT LIKE '2__' ---- the added line

Almost. If INMESS can be NULL your INSERT will not include those rows,
while the original scheme would retain them.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]