Re: SELECT then DELETE versus extra clause in SELECT
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 29 Nov 2007 22:03:59 +0000 (UTC)
metaperl (metaperl@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- SELECT then DELETE versus extra clause in SELECT
- From: metaperl
- SELECT then DELETE versus extra clause in SELECT
- Prev by Date: Re: Distinct Name with Date Order By Date
- Next by Date: Windows 2003 SP1 won't let me set the lock pages in memory option...it says I need Sp2.
- Previous by thread: SELECT then DELETE versus extra clause in SELECT
- Next by thread: Windows 2003 SP1 won't let me set the lock pages in memory option...it says I need Sp2.
- Index(es):
Relevant Pages
|
