SELECT then DELETE versus extra clause in SELECT

[metaperl]

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

===== original 3 steps (mentioned above) =====

CREATE TABLE #details (
rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
orig VARCHAR(35) NULL,
bene VARCHAR(35) NULL,
orig_corr TINYINT NULL,
bene_corr TINYINT NULL
)

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 )

DELETE d
FROM #details d
JOIN viewD v ON ( d.rec_id = v.rec_id )
WHERE INMESS LIKE '2__'

[Erland Sommarskog]

metaperl (me******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx