May 15, 2009 at 9:40 am
I have a need to update a reference table of values from a temporary table.
The reference table (tblFileNumber) has two fields:
FileNumberID (PK)
FileNumber (varchar)
The temporary table (tblUpdates) has 22 columns, one of which is FileNumberRF. Before truncating the data in the tblUpdates table, I need to search the tblFileNumber to see if each FileNumberRF value is already listed in the tblFileNumber table. If not, I need to add it.
I've read up on If Exist statements. However, I am not sure how to put it together in this case. Any suggestions would be welcome. Thanks.
May 15, 2009 at 9:48 am
Sounds fairly simple, if I understand everything. There are a couple of syntaxes you could use to do it, but. in my opinion, the easiest to understand is this one:
-- i am assuming that teh FileNumberID is an Identity() Column
INSERT INTO tblFileNumber
(
FileNumber
)
SELECT
TU.FileNumberRF
FROM
/*
Left Outer Join says give me all rows in the
first table regardless of if there is a match
in the second table.
*/
tblUpdates AS TU LEFT OUTER JOIN
tblFileNumber AS TFN ON
TU.FileNumberRF = TFN.FileNumber
WHERE
-- eliminates rows with a match
TFN.FileNumber IS NULL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 15, 2009 at 9:50 am
Damn jack, you beat me to it, 😉 Good job!
Cheers,
J-F
May 15, 2009 at 10:10 am
Or using EXISTS as was mentioned.
INSERT INTO tblFileNumber
SELECT TU.FileNumberRF
FROM tblUpdates AS TU
WHERE NOT EXISTS (SELECT * FROM tblFileNumber AS TFN
WHERE TFN.FileNumber= TU.FileNumberRF )
May 15, 2009 at 10:21 am
J-F,
I was wondering how close we came.
Tom,
Yeah that is a good solution as well, I just think for newer developers the LEFT JOIN syntax is easier to understand. I actually use the EXISTS/NOT EXISTS more often than the LEFT JOIN syntax in my own development now.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 18, 2009 at 11:20 am
I got the Insert into suggestion working without a problem. Question: something I should have mentioned is that there are a couple of million FileNumbers in the reference table (indexed of course) and each day it is possible to add a few thousand more File Numbers to the reference list. In terms of performance, would the “if exist” statement work better or just about the same as using the insert into?
May 18, 2009 at 12:20 pm
You would need to test that situation. It appears that there can be a performance benefit by using EXISTS/NOT EXISTS, but you should test both before you make a final decision.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply