Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select only records that do not exist

Author  Topic 

MatthewB
Starting Member

5 Posts

Posted - 2014-01-24 : 15:30:52
I am trying to write a script that will only insert records that do not exist to a local table. Whenever a form is changed, it keeps the same name, but a new record is created with a new ID. I need to be able to pull the new record by checking for ID's that are not on the local table, but using a form name to isolate. The nested select I am attempting is

select distinct qf.[eform_id]

, qf.[name]

, qf.[description]

from [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf

inner join FORMS_REF fr on qf.[name] collate database_default = fr.form_name collate database_default

where not exists (select fr.eform_id

from forms_ref fr

inner join [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf on fr.eform_id = qf.eform_id);


It is outputting nothing, but I have intentionally deleted one form record to force it to show up. Any ideas?

Thank you!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-01-24 : 16:33:04
One way to find rows that exist in one table but not another is with this (left outer join where null) model:

select ar.<colList>
from tableWithAllRows ar
LEFT OUTER JOIN
tablewithSomeRows sr
on sr.<correlationCols> = ar.<correlationCols>
where sr.<correlationCol> IS NULL


just as a warning: JOINs across linked servers can be extremely inefficient

Be One with the Optimizer
TG
Go to Top of Page

MatthewB
Starting Member

5 Posts

Posted - 2014-01-24 : 17:56:46
Thanks TG. I do look for opportunities to make this data pull more efficient, and this is just one element of it. So far, I have not been able to find a better method to limit the data than creating a join with the linked server, but I am open to any ideas.

I will change the script as you have outlined. I appreciate the assistance!
Go to Top of Page

MatthewB
Starting Member

5 Posts

Posted - 2014-01-24 : 18:17:06
Having said that...would it be more efficient to build a temporary table on the remote server prior to these operations, and use that as a means of isolating data from the other tables?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-27 : 07:29:00
quote:
Originally posted by MatthewB

Having said that...would it be more efficient to build a temporary table on the remote server prior to these operations, and use that as a means of isolating data from the other tables?


yep. that would definitely help. Also make sure you add proper indexes on it like columns used for joining etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -