November 23, 2005 at 5:06 am
I created a stored procedure where it copies data from one table to another. This would be done on a regular basis. a emaple of which is:
insert into tableA (field1,field2)
select field1, field2
from tableB
where field1 not in (select field1 from tableB)
I have been advised that when tableB has many records it would perform better would try and do it by using a left join from the tableA to the tableB table and testing for a null value for the field1 in tableB.
What are other's thoughts?
Robert T Turner
November 23, 2005 at 2:30 pm
*I'm wonding if the query will work at all
from tableB
where field1 not in (select field1 from tableB) /* every record eliminated + a subselect on each record executed because of IN*/
*insert into tableA (field1,field2)
select field1, field2
from tableB /*source*/
left join tableA /*destination*/
on tableB.field1=tableA.field1 /*compare on field1*/
/*extra meausures required when tableB.field1 can be null*/
where tableA.field1 IS NULL /*testing for existence,may not be in tableA*/
November 23, 2005 at 3:04 pm
Please see the posts with a subject of "LEFT OUTER JOIN IS NULL or WHERE xx NOT IN(select..)" at
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=236939#bm237152
To summarize the posts:
The "NOT IN" vs "LEFT OUTER JOIN" are NOT equivalent and may produce difference results due to nulls.
"NOT EXISTS" is another alternative, is slightly faster and is easier to understand.
In your case, here is an example SQL statement:
select *some columns*
from SourceTable
where NOT EXISTS
(select 1
from TargetTable
where TargetTable.Key = SourceTable.Key
)
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply