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

• Next in forum: Windows 2003 SP1 won't let me set the lock pages in memory option...it says I need Sp2.
• Prev in forum: Re: Getting System Time From SQL Server
• Thread view: Re: SELECT then DELETE versus extra clause in SELECT

[Reply to this message]