SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select only records that do not exist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MatthewB
Starting Member

USA
5 Posts

Posted - 01/24/2014 :  15:30:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/24/2014 :  16:33:04  Show Profile  Reply with Quote
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

Edited by - TG on 01/24/2014 16:33:41
Go to Top of Page

MatthewB
Starting Member

USA
5 Posts

Posted - 01/24/2014 :  17:56:46  Show Profile  Reply with Quote
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

USA
5 Posts

Posted - 01/24/2014 :  18:17:06  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/27/2014 :  07:29:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000